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 <brian@stripped>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=1
>
>
--
-----------------------------
Johnny Withers
601.209.4985
johnny@stripped
