Re: sql problem

[Daniel Kasak]

Dean A. Hoover wrote:
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?
I'm a little unsure what you're asking now.
If I'm interpreting your correctly, you just have to do:
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
where parent.grandparent_id='Fred'
group by parent.id;

That will only give you *records* where parent.grandparent_id is 'Fred'.
If you want to return records for *ALL* rows, but only want the count to 
*increment* when parent.grandparent_id='Fred' then you'd do something like:

select parent.id, parent.name, sum(if(child.parent_id is null, 0, 
if(parent.grandparent_id='Fred', 1, 0))) as CountChildren
from parent left join child on parent.id=child.parent_id
group by parent.id;

Or an equivalent way ( this replaces the sum() statement above ):
sum(
   case when child.parent_id is null
      then 0
   else
      if parent.grandparent_id='Fred'
         then 1
         else 0
      end
   end
) as CountChildren
which should also work ( untested but looks right ). I use this when I 
get a couple of layers of if() statements - it's a lot easier to understand.

--
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]