I am having trouble joining a table twice on the same column.

Table People

Table Phone_calls

Desired output

SELECT name as caller_name, name as receiver_name, duration
FROM people
JOIN phone_calls ON people.phone_number=phone_calls.caller
JOIN phone_calls ON people.phone_number=phone_calls.receiver
ORDER BY duration;

How should differentiate the two name links I want to output?

CodePudding user response：

This is where aliases make for an easier query:

select pc.duration, c.Name as Caller_name, r.Name as Receiver_name
from Phone_calls pc
join People c on c.phone_number = pc.caller
join People r on r.phone_number = pc.receiver
order by pc.duration;

CodePudding user response：

I'm not sure if I understood your question correctly but, assuming the desired output you've mentioned is indeed the output you desire, this query correctly returns your output:

create table people (
    nome varchar(50) primary key,
    phone_number varchar(50)
);

insert into people values ('Pamela', '113 555 7544');
insert into people values ('Jordan', '328 555 9658');
insert into people values ('Haley', '502 555 6712');

select * from people;

create table phone_calls (
    caller varchar(50),
    receiver varchar(50),
    durations int
);

insert into phone_calls values ('113 555 7544', '328 555 9658', 234);
insert into phone_calls values ('328 555 9658', '502 555 6712', 500);
insert into phone_calls values ('502 555 6712', '113 555 7544', 468);

select 
tt.durations, 
tt.caller_name,
p.nome as "receiver_name"
from 
(
    select 
        pc.durations,
        p.nome as caller_name,
        pc.receiver as telefone
from people p 
left join phone_calls pc
on p.phone_number = pc.caller
) tt
left join people p
on tt.telefone = p.phone_number
where tt.caller_name = 'Pamela';