getting different counts in one query
Posted: Thu Mar 10, 2005 12:28 pm
ok lets say I have two tables person and pet and I want to find out how many cats and dogs each person has. So I do this
The problem I am having is that The counts aren't working. If there is only one dog, but two cats, I still get a count of two dogs.
Code: Select all
SELECT Person_Name, COUNT(Cats.PetID) AS cat_count, COUNT(Dogs.PetID) AS dog_count
FROM Person
LEFT JOIN Pets Cats ON Cats.OwnerID = Person.Person_ID AND Cats.Type = CAT
LEFT JOIN Pets Dogs ON Dogs.OwnerID = Person.Person_ID AND Dogs.Type = DOG