Left Joins Produce Data Reports Using All Rows of One Table

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.

Not all members are listed in the report.

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 “)

All members present in the report using LEFT JOINs.

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!

Leave a Reply

Your email address will not be published. Required fields are marked *