Now, to complicate things further, suppose there is
yet another table (grandparent, let's say) and there is
a column in parent that is grandparent_id. I only want
results where parent.grandparent_id = 'Fred'. How do
I do that? Is it with a WHERE clause or some other
SQL magic?
Dean
Daniel Kasak wrote:
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 ).
------------------------------------------------------------------------
------------------------------------------------------------------------
The following links have been inserted by the NUS Consulting internal
spam filter and are for NUS Consulting staff only
Spam
<http://screamer.nusconsulting.com.au/internal/canit/b.php?c=s&i=97064&m=2727188145c2>
Not spam
<http://screamer.nusconsulting.com.au/internal/canit/b.php?c=n&i=97064&m=2727188145c2>
Forget previous vote
<http://screamer.nusconsulting.com.au/internal/canit/b.php?c=f&i=97064&m=2727188145c2>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
