List:General Discussion« Previous MessageNext Message »
From:Johnny Withers Date:November 2 2009 3:05pm
Subject:Re: Trouble joining 3 tables
View as plain text  
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

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