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


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 industry, region, COUNT(*) 
FROM labour_inspections l
left join company_industries i on 
left join regions r on 
where != 'NULL' and != 'NULL' 

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:


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