Jim Grill 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
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
Other way around. Put the 'if' statement *inside* the 'count' statement,
and change 'none' to a numeric zero ( 0 ).
--
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]
