Searching through different tables?-CodePudding

I have several tables with a Sequence as the primary key.

They are unrelated at all, there are no foreign keys or anything, or anything that could link one to another, so join won't work.

Union won't work either because they are basically different tables.

I know that you could assign "null" to those columns that exist in one table but not another, if that's the case, I could just make everything into one big table with `Sparse Columns', but I just "personally" don't like a table with too many null values.

The only relationship they got is that they share a Sequence as a primary key, so they all have a unique primary key, even across each other.

So, how can I "effectively" search through all these tables for the unique primary key I'm looking for instead of selecting through each table until a non-null result is returned?

Thank you very much for your help!

CodePudding user response：

If it suits you, something with dynamic query can look like this:

create table tableA ( id int, col1 char ,col2 char );
create table tableB ( id int, col3 int ,col4 int );
create table tableC ( id int, col5 bit ,col6 bit );

insert tableA values (1, 'a', 'b');
insert tableB values (2, 2, 2);
insert tableC values (3, 0, 1);
 
declare @sql nvarchar(max);

with cte_union AS 
(
    select id, 'tableA' as t from tableA
    union all
    select id, 'tableB' from tableB
    union all
    select id, 'tableC' from tableC
)
select @sql = 'SELECT * FROM '   t   ' WHERE id = '   CAST(id AS nvarchar(MAX)) 
from cte_union 
where id = 1;   --your @id param

exec sp_executesql @sql;

CodePudding user response：

If you're just looking to find which table has the ID?
Then you could LEFT JOIN each table to a query with the number.

SELECT q.id
, COUNT(a.id) AS A
, COUNT(b.id) AS B
, COUNT(c.id) AS C
, COUNT(d.id) AS D
, CASE 
  WHEN COUNT(a.id) > 0 THEN 'TableA'
  WHEN COUNT(b.id) > 0 THEN 'TableB'
  WHEN COUNT(c.id) > 0 THEN 'TableC'
  WHEN COUNT(d.id) > 0 THEN 'TableD'
  END AS Tbl
FROM (select 123 as id) q
LEFT JOIN tableA a ON a.id = q.id
LEFT JOIN tableB b ON b.id = q.id
LEFT JOIN tableC c ON c.id = q.id
LEFT JOIN tableD d ON d.id = q.id
GROUP BY q.id

Then select from the table that has the id.

F.e. the query above could return a result like :

id     A    B    C    D    Tbl
------ ---- ---- ---- ---- -------------
123    0    1    0    0    TableB

So TableB would have id = 123

CodePudding user response：

If by "effectively" you mean performance then loop through all tables and break when the result set isn't empty.