A client needed to have several reports created that indicate the various training sessions and certifications that each member has achieved. Producing the reports required collating data from several mysql tables and that made the queries a little complex.
SELECT statements were used for the most part which combined data from up to nine different mysql tables for the most inclusive report. A problem surfaced in that the reports did not show all the members. Some of the newer members did not yet have accomplishments that would show up in the output. Instead of seeing blank values for the newbies, the output did not list those new people at all. The reports were incomplete without listing all the members.
It would be helpful to see those blanks in the report as a reminder that some of the new people still need to satisfy certain requirements.
LEFT JOINS to the rescue!
LEFT JOINs are used to combine data from several database tables that may or may not have content pertaining to all members. Using the LEFT JOIN having the members table on the left will retain all members in the final report whether or not they have entries in the associated tables.
Instead of using the regular construct, SELECT data-col1, data-col2, data-col3 FROM table1, table2 WHERE condition1 = condition2, a LEFT JOIN was used like so:
SELECT data-col1, data-col2, data-col3 FROM table1 LEFT OUTER JOIN table2 ON condition1 = condition2
where the two table names are separated by “LEFT OUTER JOIN” and the WHERE clause is changed to the ON condition.
Emphasis is given to the table named on the left, table1, so that all its rows will be retained through the SELECTing of columns even though null values are encountered.
Original code that produced a report without blank lines:
$fems = mysql_query(“SELECT `last_name`, `first_name`, MAX(`certs_ID`) AS mcrt, `fema_date`, `pin` FROM `members`, `fema`, `fema_attend`, `certs`, `sar-certs` WHERE `pin` = `mem_pin` AND `fema_ID` = `fema_ids` AND `member_pin` = `pin` AND `cert_ID` = `certs_ID` GROUP BY `last_name`, `first_name` “)
Updated code using LEFT JOINS that produced a complete report that contained blank lines:
$fems = mysql_query(“SELECT `last_name`, `first_name`, MAX(`certs_ID`) AS mcrt, `fema_date`, `pin` FROM `members` LEFT OUTER JOIN `fema_attend` ON `pin` = `mem_pin` LEFT OUTER JOIN `fema` ON `fema_ID` = `fema_ids` LEFT OUTER JOIN `sar-certs` ON `member_pin` = `pin` LEFT OUTER JOIN `certs` ON `cert_ID` = `certs_ID` GROUP BY `last_name`, `first_name` “)
You’ll need a new LEFT JOIN for each table that you want to tack onto the SELECT statement in order to keep the blank values in the output.
Keep smiling and keep reading!