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
and this my child table
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
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.