List: General Discussion « Previous MessageNext Message » From: Brian Dunning Date: November 2 2009 4:53am Subject: Trouble joining 3 tables View as plain text
```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?
```