Hi, Can this problem be solved using SQL rather than writing a program?

My problem is obviously slightly more complicated than I'm showing here,
but this is the gist of it:

Given the following two tables:

Table: pets With one column pet_type with rows containing the values:
Dog, Cat, Bird, Fish

Then another table pet_owners with two colums pet_owner and pet_type
both strings.

Assume the pet_owners table has the following rows:

Adam, Dog
Adam, Cat
Adam, Fish
Adam, Bird
Mark, Cat
Mark, Bird
Mark Fish
Steve, Dog
Steve, Bird


I want to find the pet_owners who do not have a full set of pets as
defined in the pets table, and which ones they are missing.

So the output of the solution would be:

Mark, Dog
Steve, Cat
Steve, Fish

I can get a unique list of pet_owner and a count of how many pets they
have, but actually identifying which exact ones are missing for each
person is causing me the trouble.

Any thoughts, or will it be better to solve this problem in code?

In real life the problem the "pets" table has approx 50 rows and the
"pet_owner" is approx 20,000 rows so it's not a huge dataset.

Cheers,

Martin

Something like this:
Code:
--------------------
select distinct o.pet_owner, p.pet_type
from pet_owners o, pets p
where p.pet_type not in
(select pet_type from pet_owners o2
where o2.pet_owner = o.pet_owner)
--------------------


--
Bodo

On Sep 4, 2012, at 11:59 AM, Martin Crouch wrote:

Well, as long as the row counts remain reasonable, you can do:

select cp.pet_owner, cp.pet_type
from (select dpo.pet_owner, dpp.pet_type
from (select distinct pet_owner from pet_owners) dp, pets dpp) cp
left join pet_owners pown on cp.pet_owner = pown.pet_owner and cp.pet_type=pown.pet_type
where pown.tid is null;

which constructs the cross product of (distinct) pet owners and pet types,
and finds the ones that aren't in the actual pet owner table. The left join / tid
is null business is just another way of writing a not exists and you can do
it with not exists if you like.

If the exceptions will be relatively rare, you can help the server do less work
by replacing the (select distinct pet_owner from pet_owners)
with (select pet_owner from pet_owners group by pet_owner
having count(*) < (select count(*) from pets))

I haven't tried any of this but it ought to be close enough to get the
idea across, I hope.

Karl

Apologies if you see this twice, I posted it yesterday but I can't see
that it's appeared...


Thanks for the replies. I have managed to solve my problem now using
your suggestions.

Basically create a cartesian product of all the possibilities that I
want to find. Then use "not exists" or "not in" to find those that
actually don't exist on the database.

Very clever!

Cheers.