How to count rows in one table based on another table in mysql

Question

I have two tables in a MySQL database. The first has a list of department names.

departments abbreviation | name -------------|------------- ACC | accounting BUS | business ... 

The second table contains a list of courses with names that contain the abbreviation for the department.

 courses section | name -------------|------------- ACC-101-01 | Intro to Accounting ACC-110-01 | More accounting BUS-200-02 | Business etc. ... 

I would like to write a query that for each row of the departments table will give me the number of rows in the courses table as the abbreviation I have. Something like that:

  abbreviation | num -------------|-------------- ACC | 2 BUS | 1 ... 

I can do this for one separate department with a request

 SELECT COUNT(*) FROM courses WHERE section LIKE '%ACC%' (gives me 2) 

Although I could scroll in PHP and execute the above query many times, I would prefer to do this in a single query. This is the pseudo code I'm thinking about ...

 SELECT department.abbreviation, num FROM for each row in departments SELECT COUNT(*) AS num FROM classes WHERE section LIKE CONCAT('%',departments.abbreviation,'%) 

Any ideas?

Answer 1

 SELECT d.abbreviation, COUNT(*) num FROM departments d INNER JOIN courses c ON c.section LIKE CONCAT(d.abbreviation, "%") GROUP BY d.abbreviation 

Sql fiddle

Answer 2

A quick fix, but not the best, might be:

 SELECT abbreviation, (SELECT COUNT(*) FROM courses C WHERE D.abbreviation = SUBSTRING(C.abbreviation, 0, 3)) AS c FROM departments D; 

Answer 3

Take a picture:

 select d.abbreviation, count(d.abbreviation) count from departments d inner join courses c on (LOCATE(d.abbreviation,c.section) <> 0) group by d.abbreviation;