I need to join to a table and if no matches join to another table.

Main Table - A

Table - B

Table - C

In my sample data above I would need result set to be:

I've tried left joining Table A to Table B. This gives me a null DataId for the 2nd record - understandably. I could do another left join to Table C. Is that the right approach? Or is joining to a table and if no matches join to another table viable?

CodePudding user response：

I'd left join on both tables and coalesce the idb columns:

SELECT    a.*, COALESCE(b.idb, c.idb)
FROM      a
LEFT JOIN b ON a.code = b.code
LEFT JOIN c ON a.code = c.code

CodePudding user response：

in case you want to return match records you can use the INNER JOIN SQL statement it will dynamically look for match rows between given tables and return the match records like below's peace of codes:

SELECT *
FROM `table_a`
INNER JOIN `table_b` ON table_a.code = table_b.code
INNER JOIN `table_c` ON table_a.code = table_c.code