Variable table names in a single query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Variable table names in a single query

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-12-22 : 07:46:39

Toby writes "Suppose we have a number of tables where the first table contains information that all of the other tables must have but each of the other tables is unique in some way. Imagine also, that I only want to write one query to get at the data and that I want the query to contain no database-specific syntax, i.e. I want to maintain database independence. This is the problem I’m having and, as I’m no expert in SQL (yet), I’d be grateful for any assistance. Below, I’ve attempted to set out the problem such that it is clear to understand.
Table 1 looks something like this:
CREATE TABLE item
(
item_code char(3) NOT NULL,
item_table char(80) NOT NULL
CONSTRAINT item_pkey PRIMARY KEY (item_code)
)
And table 2 looks something like this:
CREATE TABLE english_data
(
Item_code char(3) NOT NULL,
types_of_rain int2 NOT NULL,
population_of_london int4 NOT NULL
CONSTRAINT english_data_pkey PRIMARY KEY (item_code)
)
And table 3 looks something like this:
CREATE TABLE russian_data
(
item_code char(3) NOT NULL,
polonium210_ytd int2 NOT NULL,
num_bananas__ytd int4 NOT NULL
CONSTRAINT Russian_data_pkey PRIMARY KEY (item_code)
)
And so on, and so forth.
Now, suppose that all I know at run time is the item code but I want to be able to pull back the full join of any two tables where the first table is always the ”item” table, so one query would look like this:

Select *
From item as a
, english_data as b
Where item_code = “UK”
And a.item_code = b.item_code
Result:
item_code item_table type_of_rain pop_of_london
UK english_data wet 40,000

Whereas, the another would look like this:
Select *
From item as a
, russian_data as b
Where item_code = “RU”
And a.item_code = b.item_code
Both of which yield different results. What I’d like to do, is something like this but I don’t know how:

Select *
From item as a
, [a.item_table] as b
Where a.item_code = b.item_code

I’m pretty sure I can’t do this as one pass at the database. If I use two passes I can do it but that is orders of magnitude slower than I need it to be.
Can I store a separate column on the “items” table that contains the SQL that I need that I could execute directly as a piece of SQL from my program but am not sure how to do this or if it is efficient. Something like this:
CREATE TABLE item
(
item_code char(3) NOT NULL,
item_SQL char(400) NOT NULL
CONSTRAINT item_pkey PRIMARY KEY (item_code)
)
Where the table would then look something like this:
Item_code item_SQL
------------ -------------
Row1: “UK” “select * from item as a, english_data as b where a.item_code = b.item_code”
Row2: “RU” “select * from item as a, russian_data as b where a.item_code = b.item_code”
Maybe there’s a better way? Doe you know? This is driving me nuts. And before you ask, the “item” table has rather more than I’ve shown here."

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-22 : 07:58:33

You could use LEFT JOIN to join in both english_data and russian_data tables.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources