SQL:将协助同一事件的人结合起来

Question

create table people(
  id_pers int,
  nom_pers char(25),
  d_nais date,
  d_mort date,
  primary key(id_pers)
);

create table event(
  id_evn int,
  primary key(id_evn)
);

create table assisted_to(
  id_pers int,
  id_evn int,
  foreign key (id_pers) references people(id_pers),
  foreign key (id_evn) references event(id_evn)
);

insert into people(id_pers, nom_pers, d_nais, d_mort) values (1, 'A', current_date - integer '20', current_date);
insert into people(id_pers, nom_pers, d_nais, d_mort) values (2, 'B', current_date - integer '50', current_date - integer '20');
insert into people(id_pers, nom_pers, d_nais, d_mort) values (3, 'C', current_date - integer '25', current_date - integer '20');

insert into event(id_evn) values (1);
insert into event(id_evn) values (2);
insert into event(id_evn) values (3);
insert into event(id_evn) values (4);
insert into event(id_evn) values (5);

insert into assisted_to(id_pers, id_evn) values (1, 5);
insert into assisted_to(id_pers, id_evn) values (2, 5);
insert into assisted_to(id_pers, id_evn) values (2, 4);
insert into assisted_to(id_pers, id_evn) values (3, 5);
insert into assisted_to(id_pers, id_evn) values (3, 4);
insert into assisted_to(id_pers, id_evn) values (3, 3);

我需要找到在任何特定日期为同一事件提供协助的夫妇。

我试过:

select p1.id_pers, p2.id_pers from people p1, people p2, assisted_event ae
where ae.id_pers = p1.id_pers
and   ae.id_pers = p2.id_pers

但返回 0 行。

我做错了什么？

Answer 1

试试这个:

 select distint ae.id_evn, 
    p1.nom_pers personA, p2.nom_pers PersonB
 from assieted_to ae
     Join people p1 
        On p1.id_pers = ae.id_pers 
     Join people p2 
        On p2.id_pers = ae.id_pers
           And p2.id_pers > p1.id_pers

这会生成在同一事件中提供协助的所有成对的人 [couples]。使用您的架构，无法将结果限制为他们在同一天提供协助的案例。假设是，如果他们协助了同一事件，那么该事件只能发生在一天内。