If a row does not exist, check a second column on a join

Question

I'm trying to join a table on a column

Select * From tableA a LEFT JOIN tableB b ON a.key = b.foreignkey ...

If that row cannot be found I want to join it to another column in table B. Is there an efficient way to do this.

Thanks

sacse · Accepted Answer · 2020-04-27 16:42:53Z

You can use COALESCE to join on the first non-NULL column.

Select * From tableA a LEFT JOIN tableB b ON a.key = COALESCE(b.foreignkey, b.another_column, b.another_column2)

It will try to join with b.foreignkey first. if that is NULL then b.foreignkey and so on.

Please find the example here.

Gordon Linoff · Accepted Answer · 2020-04-27 16:23:05Z

Add another LEFT JOIN:

Select * From tableA a left join tableB b on a.key = b.foreignkey left join tableC c on a.key = c.foreignkey and b.foreignkey is null;

2 Answers 2