Choosing a rowset when a value exists in one of 5 tables with different numbers of columns

Question

Using SQL Server, I need to return the entire row from any table containing a “value” in the “File Name” column (the column that contains each of the tables), but the tables do not have the same number of columns, and each table has unique columns with its own specific data types (The only Name / Type column that they have is the Filename column, which I need to check for "value").

Ideally, I could do something like: SELECT * FROM Table1, Table2, Table3, Table4, Table5 WHERE Filename = 'someValue' Since all tables have the same column name for the file name.

I tried using Union, but it has problems, since the number of columns and table data types does not align. I also tried every JOIN combination I could find.

I am sure that this can be done with IF EXISTS, but it will be many, many lines that seem unnecessary. Hoping there is a more elegant solution. Thanks in advance!

Answer 1

You can try to join tables. First create a temporary table in which you store the input. And then join the tables with this temporary to get all the records you want. When there is no entry in the table for this file name, you will get NULL values.

create table Table1 (id int,value int);
insert into Table1 values (1,10)

create table Table2 (id int,value int);
insert into Table2 values (1,20)

create table Table3 (id int,value int);
insert into Table3 values (2,30)

Here is the request itself

create table #tmp (id int)
insert into #tmp
values (1)

select t.id, t1.value, t2.value, t3.value from #tmp as t

left join Table1 as t1
on t.id = t1.id
left join Table2 as t2
on t.id = t2.id
left join Table3 as t3
on t.id = t3.id

And here is what you get

id  value   value   value
 1     10      20    NULL

Answer 2

:

EXEC sp_MSforeachtable 
@command1='SELECT * FROM ? where filename = ''someValue''',
@whereand='AND o.id in (select object_id from sys.tables where name in (''Table1'',''Table2'',''Table3''))'