CODEWITHSUNDEEP
sqlsql-serverselect
Albert
Albert

Reputation: 161

from statement based on table name got from another table

I have two tables:

table1 
-id 1
-name animals

animals
-id 1
-age 13

Now I want to create sql statement something like this:

select age from (select name from table1 where id = 1)

It is posible to do this in ms sql ?

Regards

Upvotes: 0

Views: 1171

Answers (3)

garnertb
garnertb

Reputation: 9584

What I think you want it:

SELECT T1.NAME, T2.AGE 
FROM TABLE1 T1 
INNER JOIN ANIMALS T2 ON T1.ID=T2.ID 
WHERE ID=1

Upvotes: 0

Blorgbeard
Blorgbeard

Reputation: 103467

It is possible only using dynamic sql.

declare @sql nvarchar(max)
select @sql = 'select age from ' + name from table1 where id=1    
exec sp_executesql @sql

Note that this is not a good idea in general, and you'd be much better off changing your design as per tvanfosson's answer.

Upvotes: 1

tvanfosson
tvanfosson

Reputation: 532465

I think this is a bad design. I'd use a key to tie just two tables together:

Categories

 ID    Type
 1     Animal
 2     Person
 3     Building

Things

 ID    Type      Name                   Age
 1     Animal    Fluffy                 13
 2     Person    Joe                    23
 3     Animal    Lucy                   3
 4     Building  Empire State Building  80

The your query would be:

select age
from categories c
    inner join things t on c.Type = t.Type
where c.ID = 1

Add an index on the FK (join) column in Things to make this fast.

Upvotes: 4

Related Questions