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

Question

I checked this answer but I have a problem.

SQL Fiddle (I changed INNER JOIN to LEFT JOIN)

What if there is a row in my departments (DDD for example) and no association in the courses table .

As you can see in the fiddle the count for DDD department is 1 like the BUS department has even if there is no data for DDD department. I know that LEFT JOIN does this but how can I modify this ?

How do I get count = 0 for the DDD department?

M Khalid Junaid · Accepted Answer · 2014-07-09 14:58:04Z

1

Try COUNT(c.section) so when section results null for no association then in count it evaluates to zero

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

Demo

answered Jul 9, 2014 at 14:58

M Khalid Junaid

64.6k10 gold badges93 silver badges120 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Demo

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related