NxM table query

Question

为了说明我的问题，考虑以下三个表:<标题>:

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

<标题>宠物:

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

<标题>所有权:

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)

和元组:

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

我想要一个只返回拥有狗和猫的主人的查询，在本例中是carly。宠物的数目可以多于这两个

Answer 1

有几种方法可以做到这一点，包括使用两个exists条件。不过，我个人最喜欢的是查询哪些主人养猫或狗，并计算他们养宠物的不同数量:

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