AFAIK, the problem is in the Group statement. You can't count on the grouped field. You need to group by ad.id and then use group functions (max(name) etc... to get the info, and count on review...
That's how I would do it. --Brian On Fri, 14 Dec 2001, Jon Drukman wrote: > i'm trying to join 3 tables. tables 1 and 2 contain information about a > person, table 3 contains reviews of that person and may not have any > data. outer join, right? i can't get it to work though. > > the tricky part is i don't want to return the actual data from the 3rd > table, i just want the number of matching rows. is it possible to do in > one query? here's what i have now: > > SELECT ad.id, name, age, audiogallery, DATE_FORMAT(ad.mtime,"%m/%d"), > commentary1, count(review.id) > FROM ad LEFT OUTER JOIN review ON ad.id=review.id, person, category, city > WHERE cat = 'Singer' > AND city = 38 > AND adtype in (2,4,6) > AND ad.id=person.id > AND ad.id=category.id > AND ad.id=city.id > GROUP BY review.id > ORDER BY age > > the part that's messing me up is the count(review.id) -- what am i doing wrong? > > -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
