patodomau patodomau - 5 months ago 9
SQL Question

NxM table query

In order to ilustrate my problem, consider these three tables:#

Person:



personid int auto_increment not null,
firstname varchar(16) not null,
constraint pk_person primary key (personid)


Pet:



petid int auto_increment not null,
petname varchar(16) not null,
constraint pk_pet primary key (petid)


Ownership:



owner int not null,
pet int not null,
constraint fk_owner_ownership foreign key (owner) references Person (personid) on delete cascade,
constraint fk_pet_ownership foreign key (pet) references Pet (petid) on delete cascade,
constraint pk_ownership primary key (owner, pet)


And the tuples:

insert into person (firstname) values ("andy");
insert into person (firstname) values ("barney");
insert into person (firstname) values ("carly");

insert into pet (petname) values ("dog");
insert into pet (petname) values ("cat");

insert into ownership (owner, pet) values (1, 1); #andy owns a dog
insert into ownership (owner, pet) values (2, 2); #barney owns a cat
insert into ownership (owner, pet) values (3, 1);
insert into ownership (owner, pet) values (3, 2); #carly owns a dog and a cat


I'd like a query that returns just the owners that owns both dog and cat, which in this case is carly. The number of pets may be more than these two.

Answer

There are a couple of ways of doing this, including using two exists conditions. My personal favorite, though, is querying which owners have cats or dogs and counting the distinct number of pets they have:

SELECT   firstname
FROM     person psn
JOIN     ownership o ON psn.personid = o.owner
JOIN     pet ON pet.petit = o.pet
WHERE    petname IN ('dog', 'cat')
GROUP BY firstname
HAVING   COUNT(DISTINCT petname) = 2