Dean A. Hoover wrote:
Suppose I have two tables: parent and child. I want to create an SQL statement that will get the count of all children belonging to parent. Is this possible in one statement? The one I have works if parent has children, but not if the number of children is 0. Here's what I have:
SELECT parent.id,parent.name,COUNT(child.parent_id) FROM PARENT INNER JOIN CHILD ON parent.id=child.parent_id GROUP BY parent.id;
Any ideas? Thanks. Dean Hoover
select parent.id, parent.name, sum(if(child.parent_id is null, 0, 1)) as CountChildren
from parent left join child on parent.id=child.parent_id
group by parent.id;
The 'left join' is important if you want to return a count even if there are no children.
-- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
