MYSQL inner join , if not match try another table

MySQLmysql-5.6PHP

I've got a very quick issue here, how can i join a table with two other tables in a way if one's info doesnt match, it goes try another one. something like:

  $properties = $mysqli->query("SELECT *
                                FROM invoices i 
                                inner join properties p on i.ref_id = p.property_id 
                                if_null_then (
                                  inner join properties2 p2 on i.ref_id = p2.property_id)  
                                ") or die (mysqli_error());

Much appreciate any response guys, i'm kinda stuck now :/

Best Answer

Always outer join them, then just use a CASE statement to pick out the info from the relevant table: 

SELECT ref_id, case when p.property_id is null then p2.property_id else p.property_id end
FROM invoices i 
left outer join properties p on i.ref_id = p.property_id 
left outer join properties2 p2 on i.ref_id = p2.property_id;
Related Question