SQL SSMS CREATE TABLE use value from another column-CodePudding

I have an exercise to create 2 tables in one code. I am creating two tables and now what I need is to use a value from the first table in the second table. Here is how it looks like:

CREATE TABLE a(
[hours]int NOT NULL,
)

CREATE TABLE b(
[example] varchar(60) NOT NULL PRIMARY KEY,
[simple_name] AS (CASE WHEN example = 'name' THEN 40 
                       WHEN example = 'name2' THEN 45 END) /*THIS IS WHAT I WANT TO DO: * [a](hours)  */,
)

How can I use the hours from the fist table in the second table? What I want is to multiply 40 or 45 by hours from the first table.

CodePudding user response：

I think a view could be your answer:

CREATE TABLE a ([hours]int NOT NULL);

CREATE TABLE b ([example] varchar(60) NOT NULL PRIMARY KEY)
GO

CREATE VIEW v as 
    SELECT
        [example],
        [simple_name] = CASE 
                            WHEN example = 'name'  THEN 40 
                            WHEN example = 'name2' THEN 45 
                        END
                        * a.hours
 
    FROM b
    CROSS JOIN a -- this is a cross join and perhaps it has to be replaced with some other types of join: inner, left, right, full?

And then use the view as a regular table:

select * from v