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

  • Replace LEFT JOIN with INNER JOIN and remove the whole WHERE clause.
    – Akina
    Commented Aug 29, 2018 at 12:45
  • Is there a way I can achieve the view in the image with a single query? MySQL knows nothing about PIVOT. Transform your data on the client side (using its report component, for example) or emulate pivot with a code in a stored procedure form (you must understand it is not easy to create such procedure).
    – Akina
    Commented Aug 29, 2018 at 12:48

1 Answer 1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.