CODEWITHSUNDEEP
sql-serversql-server-2008
Kalernor
Kalernor

Reputation: 21

How can I use an attribute from another table in defining a derived attribute in a new table?

For example: I have a table "Children" which has an attribute "age". I am now making a table "Students" which inherits only the primary key "SSN" from child, but it should have an attribute "grade" which is derived from the corresponding child's age as (Children.age - 5).

This is something I made up and tried that resulted in a syntax error:

"Subqueries are not allowed in this context. Only scalar expressions are allowed."

create table Children
(
    SSN int, 
    age int, 
    primary key(SSN)
)

create table Students
(
    SSN int, 
    grade as (select C.age from Children C, Students S where C.SSN = S.SSN) - 5, 
    primary key(SSN)
)

Even though I know it doesn't make sense because a select query will not necessarily return an int value and because it doesn't make sense to use a table in a subquery that I have not yet created, but I thought I'd add it here to see if maybe there is some sort of way to use a subquery that would work similar to this manner I attempted.

I am using SQL Server 2008.

Upvotes: 2

Views: 2374

Answers (2)

Forty3
Forty3

Reputation: 2229

Use a view:

CREATE VIEW Students AS
  SELECT c.*,
         Grade = c.age - 5
    FROM Children c

Upvotes: 1

Thorsten Dittmar
Thorsten Dittmar

Reputation: 56717

Create both tables normally and then use a view to join both tables and create a virtual column for the grade.

For example:

Select c.*, s.*, (c.age - 5) as grade from children c
Inner join student s on s.childid = c.id

Upvotes: 2

Related Questions