MySQL join on record that might not exist

Question

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?

Answer 1

You've come to a point where you noticed that there is a difference between WHERE conditions and JOIN conditions.

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

Answer 2

You need to use an outer join to include table3 instead of an inner join.

Answer 3

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