Preventing entire JOINed MYSQL query from failing when one field is missing within a WHERE clause

Question

I am doing a couple of joins with a variable in the WHERE clause. I'm not sure if I am doing everything as efficiently as I could, or even using the best practices but my issue is that half my tables have data for when tableC.type=500, and the other half don't resulting in the entire query failing.

SELECT tableA.value1 , tableB.value2, tableC.value3 FROM tableA  
JOIN tableB ON  tableB.id=tableA.id
JOIN tableC ON  tableC.id=tableB.id
WHERE tableA.category=$var && tableC.type=500;

What I would like to happen is to still get tableA.value1 and tableB.value2 even if there is no field in tableC with a type=500.
any thoughts? i'm totally stumped as how to approach this...

Answer 1

Try a LEFT JOIN

Answer 2

SELECT tableA.value1 , tableB.value2, tableC.value3 FROM tableA  
JOIN tableB ON  tableB.id=tableA.id
LEFT JOIN tableC ON  tableC.id=tableB.id AND tableC.type=500
WHERE tableA.category=$var;

[INNER] JOIN (which you have) joins only if the values exist;

LEFT JOIN returns NULL for tableC columns if no matching row in tableC is found.

I think it's necessary to move the tableC condition(s) into the ON clause when using a LEFT JOIN, as WHERE would filter out the NULL values (since tableC.type IS NULL != 500).