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?
Thread
Trouble joining 3 tablesBrian Dunning2 Nov
  • Re: Trouble joining 3 tablesJohnny Withers2 Nov
    • Re: Trouble joining 3 tablesBrian Dunning2 Nov
      • Re: Trouble joining 3 tablesJohnny Withers2 Nov
        • Re: Trouble joining 3 tablesBrian Dunning3 Nov
Re: Trouble joining 3 tablesBrian Dunning2 Nov