I'm modifying a MySQL statement but having a few troubles with LEFT JOIN and blank rows. At the moment I have:
SELECT cs.case_id, cs.child_firstname, gu.*
FROM tblcases AS cs
LEFT JOIN tblguardians AS gu
ON cs.case_id = gu.case_id
WHERE cs.child_firstname = '%craig%' AND gu.firstname LIKE '%sally%'
This query works fine as long as there is an entry in the "tblguardians" table, but the LEFT JOIN returns NULL if there is no record - therefore the entry isn't returned when I iterate over the results in PHP.
Is there a way to return the fields from "tblcases" whether or not "tblguardians" has an associated result? I think I've read too many pages of Google for much more information to sink in!
Putting a condition on a joined table in your WHERE clause effectively makes that join an INNER JOIN.
To avoid doing that you have to add the second condition to your LEFT JOIN:
SELECT cs.case_id, cs.child_firstname, gu.*
FROM tblcases cs
LEFT JOIN tblguardians gu
ON cs.case_id = gu.case_id
AND gu.firstname LIKE '%sally%' /* <- this line was moved up from WHERE */
WHERE cs.child_firstname = '%craig%'
You have to restructure your query a little. Putting a condition in the WHERE clause will always remove entire rows. Since the tblguardians entry can be null because of the LEFT JOIN, you can't compare to the firstname column in it without removing the entire row.
Since you want results even if there is no match in tblguardians, you will need to move your condition on that table to the ON clause in the JOIN. That will only remove (set to NULL) the row's result from that table, not the entire row.
SELECT cs.case_id, cs.child_firstname, gu.*
FROM tblcases AS cs
LEFT JOIN tblguardians AS gu
ON cs.case_id = gu.case_id AND gu.firstname LIKE '%sally%'
WHERE cs.child_firstname = '%craig%'
You're filtering on gu; if no entries are in the tblguardians table, no results will match that WHERE clause.
The LEFT JOIN statement permit to show only the results where there are right blank column. If you want to show both blank column, you should to use FULL OUTER JOIN. So with method you will have left and right blank column.
SELECT cs.case_id, cs.child_firstname, gu.*
FROM tblcases AS cs
FULL OUTER JOIN tblguardians AS gu
ON cs.case_id = gu.case_id
WHERE cs.child_firstname = '%craig%' AND gu.firstname LIKE '%sally%'
