SQL Select unique foreignkey from N tables

I got the following problem and want to know if it’s possible to solve it with a query in Google BigQuery.

Got this query;

SELECT destination.Id, destination.SomeColumn,
(SELECT NewId FROM table1 where OldId = destination.ForeignKeyColumn) as ForeginKeyColumn,
-- some more things
FROM MyDestinationTable destination;

I have a table named MyDestinationTable with a ForeignKeyColumn which has and old foreign key id so I need to select that ForeignKeyColumn with the new foreign key from table1 (table1 has the foreign keys old and new).

Well, that’s a part of the issue that I already solved, the problem comes in when I’ve relation with multiple tables like ForeignKeyColumn has a relation with table1 or table2 or tableN, ie;

row 1: destination.ForeignKeyColumn may have a reference ONLY in table1
row 2: destination.ForeignKeyColumn may have a reference ONLY in table3
row 3: destination.ForeignKeyColumn may have a reference ONLY in table10

The foreign key may exist ONLY in ONE table of N_tables, having this part;

(SELECT NewId FROM table1 where OldId = destination.ForeignKeyColumn) as ForeginKeyColumn,

It is possible to search the NewId across N tables until find it (just in that section)?

From table1 to tableN

Something like NewId in table1? No, then NewId in table2? No, then NewId in table3? yes, get that one as ForeginKeyColumn and don’t look on the rest of the tables (Again, the new foreign key will only exist on only one table of N tables).

If it’s possible any idea on how to do it? Need to mention I’m pretty bad on this kind of queries.

Regards!

Answer

I would suggest multiple left joins and coalesce():

select d.*, coalesce(t1.newid, t2.newid, . . . )
from MyDestinationTable d left join
     table1 t1
     on t1.oldid = d.ForeignKeyColumn left join
     table1 t2
     on t2.oldid = d.ForeignKeyColumn left join
     . . .