How do I make a join in SQL based on the table name?
So, we have a bunch of tables called like this:
training_data_001
training_data_002
training_data_003
training_data_004
training_data_005
And then, to find the ones we're looking at a field in another table, let's just call it master.training_type.
Anyway, I was wondering if anyone knows of a way to create a weird concatenation of table names with data like this. Something like that:
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 I can do this on the client side, but it would be nice if the db did it.
Also note: this is SQL Server.
Refresh . I decided to just do it client side. Anyway, thanks to everyone.
Thank!
-fREW
source to share
If the tables have the same structure, create a Partitioned View on the tables and join the view. You need to make a check constraint on the column (possibly date) so that the query optimizer can eliminate the splitting.
source to share
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 attach 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 list of tables needs to grow / shrink over time, you can dynamically create the same view based on the tables that exist by doing some lookups on the system tables.
However, this is not very efficient. Can you just use this data into a combo table at some fixed interval?
source to share
Split view is one possible approach. Since you are only selecting the foo column, are you really checking for the existence of a row in the training table via an INNER JOIN? Also, it looks like you are trying to use foo as an alias in your connection, but it is not configured that way in your SELECT clause. As a result, I am guessing what you really want.
Another question ... is the set of training tables static? Do you expect to be able to add a new table with a new suffix number and is it easy to work with?
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 really want to return columns from training data tables, you can 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
source to share