I have a table which stores data about an inspection. Inspections are carried out in a particular company belonging to an industry, located in a region. the inspections table has the industry_id field referencing the industry table and the region_id field referencing the region table.
Both industry and region tables have id and name fields.
running this query:
SELECT i.name industry, r.name region, COUNT(*)
FROM labour_inspections l
left join company_industries i on l.industry_id=i.id
left join regions r on l.region_id=r.id
where i.name != 'NULL' and r.name != 'NULL'
GROUP BY i.name and r.name
gives me the results i need but not in the way i want it for this view.
I achieved that by writing separate queries and putting the results together with PHP. It is not elegant and I am concerned about performance.
Is there a way I can achieve the view in the image with a single query? Thank you all in advance
LEFT JOIN
withINNER JOIN
and remove the wholeWHERE
clause.