You are asking for all records form all tables. So, If 1 person has 1 car and 1 pet, there will be 2 records returned for that 1 person.
You'll need to use SUM() instead of COUNT(): SELECT people.*, SUM(IF(cars.id IS NULL,0,1)) AS car_count, SUM(IF(pets.id IS NULL,0,1)) AS pet_count, .... Maybe? On Sun, Nov 1, 2009 at 10:53 PM, Brian Dunning <br...@briandunning.com>wrote: > Hi all - > > I have a table of PEOPLE, and a table of CARS owned by various people, and > a table of PETS owned by various people. Each person may have 0 or more > pets, and each person may have 0 or more cars. I'm trying to return a list > of all the people, showing how many pets each person has, and how many cars > each person has. Here's what I've got: > > SELECT > people.*, > count(cars.car_id) as car_count, > count(pets.pet_id) as pet_count > FROM > (people > LEFT OUTER JOIN > cars ON people.person_id = cars.person_id) > LEFT OUTER JOIN > pets ON people.person_id = pets.person_id > GROUP BY > people.person_id > ORDER BY > people.name > > This is giving me an alphabetized list of all the people, but the car_count > and pet_count that it's giving are only correct if either one of them is > zero. If they both have a value, then both evaluate to the two correct > values multiplied together (if Joe has 2 cars and 4 pets, it will say he has > 8 cars and 8 pets). > > My brain is melting. What am I doing wrong? > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=joh...@pixelated.net > > -- ----------------------------- Johnny Withers 601.209.4985 joh...@pixelated.net
