Trouble joining 3 tables

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=gcdmg-mysql-2@m.gmane.org

Re: Trouble joining 3 tables

--0016e6d5668945ee8a047764b5cd
Content-Type: text/plain; charset=ISO-8859-1

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 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=johnny@pixelated.net
>
>


--
-----------------------------
Johnny Withers
601.209.4985
johnny@pixelated.net

--0016e6d5668945ee8a047764b5cd--

Re: Trouble joining 3 tables

Thanks, this solved it!

On Nov 2, 2009, at 12:37 AM, Michael Dykman wrote:

> I suspect 'distinct' might help you out here.
>
> SELECT
> people.*,
> count(distinct cars.car_id) as car_count,
> count(distinct pets.pet_id) as pet_count
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Trouble joining 3 tables

--Apple-Mail-11--298281279
Content-Transfer-Encoding: 7bit
Content-Type: text/plain;
charset=us-ascii;
format=flowed;
delsp=yes

Johnny - Your solution might actually help me solve my next step,
which is to also return a count of pets bought only within the last 7
days. Something like this:

>
> SELECT people.*,
> SUM(IF(cars.id IS NULL,0,1)) AS car_count,
> SUM(IF(pets.id IS NULL,0,1)) AS pet_count,
SUM ( IF ( pets.date_bought > NOW() - INTERVAL 7 DAYS, 1, 0 ) AS
new_pet_count

Yes?


--Apple-Mail-11--298281279--

Re: Trouble joining 3 tables

--0016e6dab6d722a345047768d22d
Content-Type: text/plain; charset=ISO-8859-1

Yes, I don't see why that wouldn't work...



On Mon, Nov 2, 2009 at 11:09 AM, Brian Dunning wrote:

> Johnny - Your solution might actually help me solve my next step, which is
> to also return a count of pets bought only within the last 7 days. Something
> like this:
>
>
>
>> SELECT people.*,
>> SUM(IF(cars.id IS NULL,0,1)) AS car_count,
>> SUM(IF(pets.id IS NULL,0,1)) AS pet_count,
>>
> SUM ( IF ( pets.date_bought > NOW() - INTERVAL 7 DAYS, 1, 0 ) AS
> new_pet_count
>
> Yes?
>
>


--
-----------------------------
Johnny Withers
601.209.4985
johnny@pixelated.net

--0016e6dab6d722a345047768d22d--

Re: Trouble joining 3 tables

Darn, it's not working after all.

SELECT
people.*,
COUNT ( DISTINCT cars.car_id ) AS car_count,
COUNT ( DISTINCT pets.pet_id ) AS pet_count,
SUM ( IF ( pets.date_bought > NOW() - INTERVAL 7 DAY, 1, 0 ) ) AS
new_pet_count
WHERE...etc

car_count and pet_count are calculating correctly, but new_pet_count
is only correct if the person has 0 new pets. If they have more than
zero, new_pet_count evaluates to pet_count * 2. It seems like I need a
way to combine a "distinct pets.pet_id" with the sum.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org