join 2 table and the value in a specific manner

Posted on

Question :

I have 2 table one is master and another one is the child, now I want to write a query in such a way that all the value from the master should display and the count of that particular parent id row from the child if present otherwise it should display 0.

this my master table

enter image description here

and this my child table

enter image description here

and I have tried this:

SELECT name, COUNT(*) 
from Activity LEFT JOIN Notifications on Notifications.ActId = Activity.ActId 
GROUP BY name

and the output i got is

enter image description here

Answer :

You need to change the COUNT() so it only counts the child records (and not always the master).

SELECT Activity.name, COUNT(Notifications.ActId) 
from Activity 
LEFT JOIN Notifications on Notifications.ActId = Activity.ActId 
GROUP BY Activity.name

COUNT() won’t count NULL values (from your LEFT JOIN), so you will only count records from the Notifications table this way.

Leave a Reply

Your email address will not be published.