MySQL join on record that might not exist

join, mysql, sql

Solution

You use a left join on the table. If no corresponding record exists, the value from the table will be null, so you can use coalesce to get a value that you can compare to the string:

SELECT <columns> FROM table1 
INNER JOIN table2 ON table1.id = table2.table1_id
LEFT JOIN table3 ON table1.id = table3.table1_id
WHERE COALESCE(table3.column1, '') != 'foo' AND <other_conditions>
LIMIT 1

Problem

I'm trying to execute a query that looks similar to this: ``` SELECT <columns> FROM table1 INNER JOIN table2 ON table1.id = table2.table1_id INNER JOIN table3 ON table1.id = table3.table1_id WHERE table3.column1 != 'foo' AND <other_conditions> LIMIT 1; ``` The thing is--I want the query to return a result regardless of whether the record in `table3` exists or not. That is--if the record in `table3` is present, I want to check whether that record has a certain column value. If the record in `table3` doesn't exist, I want the query to assume that the condition is TRUE. Any pointers?

Original source