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