LEFT JOIN and group

Posted under » MySQL updated on 21 Apr 2023

The JOIN statement.

$result = mysqli_query($dbhandle,"SELECT author_name, title, year, publication_list_details.created AS lahir, publication_list_charged_cost_centres.cost_centre_id AS kostid, user_cost_centers.organization_name AS kostname FROM publication_list_details LEFT OUTER JOIN (publication_list_charged_cost_centres INNER JOIN user_cost_centers ON publication_list_charged_cost_centres.cost_centre_id=user_cost_centers.id ) ON publication_list_details.id=publication_list_charged_cost_centres.publication_list_id WHERE status = 1 ORDER BY year DESC");

This looks scarry. What I want to introduce above is () in sql query. There is this LEFT OUTER JOIN. Brackets have other uses too.

Other than alias, sometimes it is easier on the eye if you reduce the table name to just a letter.

$result = mysqli_query($dbhandle,"SELECT p.id AS publication_id, p.user_id AS group_leader, p.title, p.status AS active, p.category, p.author_name AS author, p.year, p.volume, p.pages, f.name AS pdf_url, f.data AS pdf_data, p.abstract, p.publisher_url, p.modified AS last_update, '".$updateType."' AS update_type, j.tier
FROM publication_list_details p
LEFT JOIN publication_list_files f ON p.id = f.publication_list_detail_id
LEFT JOIN publication_journals j ON j.id = p.journal_id
WHERE p.id = ".$id");

publication_list_details is being reduced to just p. The other tables being f and j which is being defined on the left join.

Take note that if the join is not valid, the record will not be shown.

There are times when there are more than one for an id so it gets repeated more than once. To just show one record and suppress repetition, you use group.

$result = mysqli_query($dbhandle,"SELECT p.id AS publication_id, p.user_id AS group_leader, 
p.title, p.status AS active, p.category, p.author_name AS author, p.year, p.volume, p.pages, f.name AS pdf_url, 
f.data AS pdf_data, p.abstract, p.publisher_url, p.modified AS last_update, '".$updateType."' AS update_type, j.tier
FROM publication_list_details p
LEFT JOIN publication_list_files f ON p.id = f.publication_list_detail_id
LEFT JOIN publication_journals j ON j.id = p.journal_id
WHERE p.id = '".$id."' GROUP BY publication_id");

Left join are better if the fields joined are not mandatory. But if it is mandatory, it is best to use INNER JOIN but it will give fewer results.

See an earlier article or see diagram below