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=arch...@jab.org
