I am attempting to combine COUNT, GROUP BY, and LEFT JOIN.. Adopting the theme on http://www.mysql.com/doc/C/o/Counting_rows.html my goal is to display only species/sex combinations that occur more than once. Additionally I wish to include the count for each row, and some keyed field from another table..
So I added a separate table "owner" containing foreign key "petID" and the field "zipcode".. A couple rows of output might look like (species=dog sex=male animalcount=3 zipcode=55555) (species=dog sex=female animalcount=2 zipcode=55555). There would be no rows with a count of 1, and zipcode can repeat.. The following statement unfortunately includes zipcode when determining uniqueness. It's as close as I've come! Any suggestions?: SELECT species, sex, zip, COUNT(*) as animalcount FROM pet LEFT JOIN owner ON pet.petID = owner.petID GROUP By species, sex HAVING animalcount > 1; __________________________________________________ Do You Yahoo!? Send FREE Valentine eCards with Yahoo! Greetings! http://greetings.yahoo.com --------------------------------------------------------------------- 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
