selecting table using subquery sql

Question

Note: This is for SQL Server 2008.

I have a table that contains a number of columns, two of which are relevant to this discussion: Object and TableID. Object is a readable name. TableID corresponds to the table associated with Object, albeit the associated tables all begin with the letter T, as in table T135 or table T143.

SchemaTable
Object    TableID   /* Object is type varchar, TableID is type int
Form1     135
Form2     143
Form3     321

I want to craft a SQL query which will select contents from these tables, based on the value of Object. Here's what I have so far (which doesn't work):

SELECT Col1, Col2
FROM (
    SELECT (T' + CAST(TableID AS VARCHAR))
    FROM SchemaTable
    WHERE Object = 'Form1'
)Tbl_T
WHERE Col1 IS NOT NULL

Can I use a subquery to select the table in this manner? If not, what else do you suggest and how?

Thank you very much for any help.

Edit

It seems that my question or query is not clear. Let me try to fix that.

I could select, say, table T135 directly like this:

SELECT Col1, Col2
FROM T135
WHERE Col1 IS NOT NULL

But what I want to do is select Table T135, based on the column Object in table SchemaTable.

You see? I don't want to select a subtable of values from table SchemaTable. I just want to grab the TableID (i.e. 135) and select content from said table (i.e. T135). There is no TableID column in table T135. TableID effectively corresponds to the name of the table. It's not used as a key or identity into other tables. I think that's where the confusion lies in understanding what I seek to accomplish.

I hope that is more clearer.

Thank you for your help.

You must use dynamic query.

Hamlet Hakobyan
– Hamlet Hakobyan

2014-05-09 19:55:40 +00:00
Commented May 9, 2014 at 19:55 — Hamlet Hakobyan
– Hamlet Hakobyan, Commented May 9, 2014 at 19:55
can you add your expected results?

rhealitycheck
– rhealitycheck

2014-05-09 20:17:20 +00:00
Commented May 9, 2014 at 20:17 — rhealitycheck
– rhealitycheck, Commented May 9, 2014 at 20:17
Thank you, Hamlet. I think that will do the trick.

user301234
– user301234

2014-05-09 20:47:27 +00:00
Commented May 9, 2014 at 20:47 — user301234
– user301234, Commented May 9, 2014 at 20:47

user3182536 · Accepted Answer · 2014-05-09 20:00:25Z

2

Yes you can use a subquery to select a table in this manner. here's another example :

select * from  
(select ndc,client_id,pkg_sz from ABC where pkg_sz=100) dist,  
(select ndc,client_id from XYZ where pkg_sz=500) trns  
where trns.client_id = dist.client_id  
and dist.ndc=trns.ndc;

answered May 9, 2014 at 20:00

user3182536

1381 silver badge12 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

selecting table using subquery sql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related