CODEWITHSUNDEEP
sqlpostgresql
Alex
Alex

Reputation: 2076

SQL: couple people who assisted to the same event

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);

I need to find couples who assisted to the same event on any particular day.

I tried: 

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

But returns 0 rows.

What am I doing wrong?

Upvotes: 1

Views: 69

Answers (3)

Craig Ringer
Craig Ringer

Reputation: 324821

When re-phrased into ANSI JOIN syntax so I can read it, your query reads:

select p1.id_pers, p2.id_pers
from assisted_event ae
inner join people p1 ON (ae.id_pers = p1.id_pers)
inner join people p2 ON (ae.id_pers = p2.id_pers)

Since id_pers is the primary key of p1, it is impossible for ae.id_pers to be simultaneously equal to p1.id_pers and p2.id_pers. You'll need to find another approach.

You don't need to join on people at all for this, though you'll probably want to in order to populate their details. You need to self-join the people-to-events join table not the people table in order to get the desired results, filtering the self-join to include only rows where the event ID is the same but the people are different. Using > rather than <> means you don't have to use another pass to filter out the (a,b) vs (b,a) pairings.

Something like:

select ae1.id_evn event_id, ae1.id_pers id_pers1, ae2.id_pers id_pers2
from assisted_to ae1
  inner join assisted_to ae2 
  on (ae2.id_evn = ae1.id_evn and ae1.id_pers > ae2.id_pers)

You can now, if desired, add additional joins on the event and persion tables to populate details. You'll need to join people twice with different aliases to populate the two different "sides". See Charles Bretana's example.

Upvotes: 1

Charles Bretana
Charles Bretana

Reputation: 146557

Try this:

 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

This generates all pairs of people [couples] who assisted on the same event. With your schema, there is no way to restrict the results to cases where they assisted on the same day. The assumption is that if they assisted on the same event, then that event can only have occurred on one day.

Upvotes: 2

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726909

You select two persons, so you need to select two assisted_event rows as well, because each person has its own assignment row in the assisted_event table. The idea is to build a link between p1 and p2 through a pair of assisted_event rows sharing the same id_evn

select p1.id_pers, p2.id_pers
from people p1, people p2
where exists (
    select *
    from assisted_event e1
    join assisted_event e2 on e1.id_evn=e2.id_evn
    where e1.id_pers=p1.id_pers and e2.id_pers=p2.id_pers
)

Upvotes: 2

Related Questions