Hi, Does this work for you? select ad.id,adtype,name,sum(review.id is not null) from person review left join ad on ad.id=review.id group by ad.id; OR select ad.id,adtype,name,sum(if (ifnull(review.id,0)=0,0,1)) from person review left join ad on ad.id=review.id group by ad.id;
Anvar. At 06:13 PM 11/02/2002 -0800, you wrote: >i have what is basically a personal ad database. > >in one table, i have a list of advertisers. in another table i have a >list of reviews. (there's a third table with stuff like their name, >address, etc). i want to get a list of advertisers and the number of >reviews all in one query. i can do it with an inner join just fine. > >select ad.id,adtype,name,count(1) from person,review,ad where >ad.id=review.id and ad.id=person.id group by review.id; > >this only selects users who have reviews though (which i understand). > >i want to do it with an outer join. if a user has no reviews it should >put a 0 in the count column. this query gets all the usernames whether or >not they have reviews, but the count column is always 1. > >select ad.id,adtype,name,count(1) from person > review left join ad on ad.id=review.id > group by ad.id; > >what am i missing? > >-jsd- > > >--------------------------------------------------------------------- >Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > >To request this thread, e-mail <[EMAIL PROTECTED]> >To unsubscribe, e-mail ><[EMAIL PROTECTED]> >Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
