CODEWITHSUNDEEP
sqlsql-servert-sql
Noob001
Noob001

Reputation: 103

Searching through different tables?

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!

Upvotes: 0

Views: 158

Answers (3)

LukStorms
LukStorms

Reputation: 29677

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

Upvotes: 1

Nenad Zivkovic
Nenad Zivkovic

Reputation: 18559

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;

Upvotes: 1

April Rain
April Rain

Reputation: 81

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

Upvotes: 0

Related Questions