user3370402 user3370402 - 3 months ago 10
SQL Question

SQL checking if value exists through multiple different tables

Lets say I have an unique 23-digit identifier of a file. I would like to check whether this identifier is present in any of the columns of all rows in all given tables. These tables are different as are their columns.

Is there some select which could help me achieve this?

Example scenario can be like this:


FileID = 534bde4c322755995941083

TableA (columnA, columnB, columnC)

TableB (columnD)

TableC (columnE, columnF)


I would like to return only those record which contain only this unique identifier and nothing more to it. The number of tables is various just like the columns.

Is there some SQL statement which could help me with this?

I can iterate through all records, columns, and tables but that seems to be like a huge overkill for such task.

Answer

you can use UNPIVOT something like this ...

SELECT Vals
FROM TableA A
 UNPIVOT( Vals FOR N IN (columnA, columnB, columnC)
         ) UP
WHERE Vals  = '534bde4c322755995941083'

UNION ALL 

SELECT ColumnD 
FROM TableB 
WHERE ColumnD =  '534bde4c322755995941083'

UNION ALL 

SELECT Vals
FROM TableC  C
 UNPIVOT( Vals FOR N IN (columnE, columnF)
         ) UP
WHERE Vals  =  '534bde4c322755995941083'