join left

Posted under » MySQL on 21 Oct 2018

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.

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.

See an earlier article
 

web security linux ubuntu GIT svn Raspberry apache mysql php drupal cake symfony javascript Ajax css