Mysql – select data from related tables and display their counts in the relating table

countjoin;MySQL

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.Desired report 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

Best Answer

name != 'NULL'

That assumes that name can be the 4-letter string 'NULL'. If, in reality, it can be the special value NULL, then do this:

name IS NOT NULL

Please provide SHOW CREATE TABLE for each table, plus a small amount of representative data including some nulls.

Related Question