SELECT from table specified in another table - possible without dynamic SQL?

Question

I've a database which contains several tables for various tables of different products. These products have unique part numbers across all tables.

To search across all tables, I've created a view which uses UNION ALL across all common fields in the tables.

Once a part has been identified, I need to select all the columns depending on the table the data resides in. The view includes a field that specifies the table the data was found in.

I'm not sure of the way to accomplish the last part:

CASE statement (I'm leaning towards this one at the moment)

Dynamic SQL (prefer not to use this, would involve SELECT * and other nasties)

SELECT in client side (client needs to select from arbitrary tables, require additional privileges, bad design?)

Alternative solution?

EDIT: Actually, IF statement is the only one that makes sense. Client shouldn't need access to the tables directly. Since the columns are different in each table anyway, might as well have a seperate statement for each table.

(I'd mark the question as answered, but I don't have enough reputation for that)

@carlgcode: http://stackoverflow.com/q/729197/27535 at least — gbn, Sep 09 '11 at 09:13
It's nasty because you don't know what columns will be returned, you don't know in what order they will be returned, and that it is against company coding policy. — squidge, Sep 09 '11 at 09:22
Can you return different columns from a View? Do they not have to be static? — cjk, Sep 09 '11 at 12:02
"Friends don't let friends write SELECT * " (Richard Campbell) — Cylon Cat, Sep 09 '11 at 12:02

score 0 · Answer 1 · answered Sep 09 '11 at 10:15

0

I am not sure whether i understood your question correctly.. my understanding is you have views which is selecting data from diffrent tables using union all.. you can give table name while creating view only

select "table1",table1.a,table1.b.. from table1 union all select "table2", table2.a,table2.b ..... from table2

answered Sep 09 '11 at 10:15

Gopal Sanodiya

204
2
3

I already have a statement like the above. The problem is that only the common fields are returned by the view. To get the non-common fields, you need to request them from the particular tables directly. I'm just not sure how to select the tables directly. The view returns the table, so I'm leaning towards using a CASE statement to run the appropriate select query (I don't want the select client-side) – squidge Sep 09 '11 at 10:28

score 0 · Accepted Answer · answered Sep 09 '11 at 19:59

0

Actually, IF statement is the only one that makes sense. Client shouldn't need access to the tables directly. Since the columns are different in each table anyway, might as well have a seperate statement for each table.

answered Sep 09 '11 at 19:59

squidge

454
2
9

SELECT from table specified in another table - possible without dynamic SQL?

2 Answers2