How do I do a join in SQL based on table name?

Question

Ok, so we have a bunch of tables that are named like this:

training_data_001
training_data_002
training_data_003
training_data_004
training_data_005

And then to find those we look at a field in another table, let's just call it master.training_type.

Anyway, I was wondering if anyone knew of a way to do a weird table name based join with this kind of data. Something like this:

SELECT foo FROM master WHERE id = ? 
INNER JOIN training_data_${master.training_type}
ON foo.id = training_data_${master.training_type}.foo_id

I know that I can do this on the client side, but it would be nice to have the db do it.

Also note: it's SQL Server.

Update: I decided to just do it on the client side. Thanks anyway everyone.

Thanks!

-fREW

Answer 1

You can only use dynamic SQL to read master.training_type to build a string that you then execute using EXEC (@stringvar)

Answer 2

You can only do it with dynamic SQL in a stored proc. You can also generate views or stored procs in advance with code generation if you don't want to do it on the fly for security or other reasons.

Answer 3

If the tables are all the same structure, create a Partitioned View across the tables and join against the view. You need to make a check constraint on a column (perhaps a date) so that the query optimiser can do partition elimination.

Answer 4

do something like this:

create view training_data_all as
select '001' as training_type, * from training_data_001
union all
select '002' as training_type, * from training_data_002
union all
select '003' as training_type, * from training_data_003
union all
select '004' as training_type, * from training_data_004
union all
select '005' as training_type, * from training_data_005

then just select and join to/from it:

SELECT foo FROM master WHERE id = ? 
INNER JOIN training_data_all
ON foo.id = training_data_all.foo_id
WHERE training_data_all.training_type = ${master.training_type}

If the table list is to grow/shrink over time, you can write this same view dynamically based on the tables that exists by doing some lookups into the system tables.

None of this is very efficient though. Can you just ETL this data into a combined table at some fixed interval?

Answer 5

The partitioned view is one possible approach. Since you are only selecting the foo column, are you really just checking for existence of a row in the training table via the INNER JOIN? Also, it looks like you're trying to use foo as an alias in your join, but it's not set up that way in your SELECT clause. As a result I'm guessing here on what you really want.

Another question... is the set of training tables static? Are you expecting to be able to add a new table with a new suffix number and have it just work?

Another possible solution:

SELECT
     foo
FROM
     dbo.master m
WHERE
     (training_type = '001' AND EXISTS (SELECT * FROM dbo.training_data_001 WHERE foo_id = m.id)) OR
     (training_type = '002' AND EXISTS (SELECT * FROM dbo.training_data_002 WHERE foo_id = m.id)) OR
     (training_type = '003' AND EXISTS (SELECT * FROM dbo.training_data_003 WHERE foo_id = m.id)) OR
     (training_type = '004' AND EXISTS (SELECT * FROM dbo.training_data_004 WHERE foo_id = m.id)) OR
     (training_type = '005' AND EXISTS (SELECT * FROM dbo.training_data_005 WHERE foo_id = m.id))

If you actually want to return columns from the training data tables then you could use something like:

SELECT
     m.id,
     COALESCE(t1.my_col, t2.my_col, t3.my_col, t4.my_col, t5.my_col) AS my_col
FROM
     dbo.master m
LEFT OUTER JOIN dbo.training_data_001 t1 ON m.training_type = '001' AND t1.foo_id = m.id
LEFT OUTER JOIN dbo.training_data_002 t1 ON m.training_type = '002' AND t2.foo_id = m.id
LEFT OUTER JOIN dbo.training_data_003 t1 ON m.training_type = '003' AND t3.foo_id = m.id
LEFT OUTER JOIN dbo.training_data_004 t1 ON m.training_type = '004' AND t4.foo_id = m.id
LEFT OUTER JOIN dbo.training_data_005 t1 ON m.training_type = '005' AND t5.foo_id = m.id