CODEWITHSUNDEEP
mysqlsqlselectcount
Jim
Jim

Reputation: 37

SQL SELECT How to get just active records

I run this query:

SELECT sites.id, count(channels.id)
FROM sites
LEFT JOIN channels on channels.site_id = sites.id
GROUP BY sites.id

..and I get this result: 1000:0; 1001:26; 1002:0; etc

I want to just include the channels that are active = 1, but when I add that criterion the sites with 0 channels no longer show.

How do I list all sites and their channel counts with channels.active = 1?

Thanks in advance.

Upvotes: 0

Views: 51

Answers (1)

Bill Gregg
Bill Gregg

Reputation: 7147

Make sure you include the check on ACTIVE in your LEFT JOIN like so:

SELECT sites.id, count(channels.id)
FROM sites
LEFT JOIN channels on sites.id = channels.site_id
AND 1 = channels.active
GROUP BY sites.id

Upvotes: 2

Related Questions