> 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 >
This is untested, but I've used this same technique for a similar problem. The only part that I am unsure of is using COUNT inside an IF. SELECT parent.id, parent.name, IF(child.parent_id IS NULL, 'none', COUNT(child.parent_id)) AS child_count FROM PARENT INNER JOIN CHILD ON parent.id=child.parent_id GROUP BY parent.id; Jim Grill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
