How to get name and surname from two id linked to same table SQL-CodePudding

I have i question about JOINign i have tables User and Category and Request

Id	name	surname
1	Alex	Morgan
2	Tom	Brady
3	Smith	Rowe

Category

Id	Category
1	Party
2	Football match

And now someone create request for another Request

Id_request	Id_caller	Id_receiver	id_category
1	1	2	1
2	1	3	2

Now i want query to find all request where user 1 (Alex) is caller and get this result

Id_request	Caller_name	Caller_surname	Receiver_name	Receiver _surname	Category
1	Alex	Morgan	Tom	Brady	Party
2	Alex	Morgan	Smith	Rowe	Football match

I try with JOIN but i didn't get this result. Pls help.

CodePudding user response：

You just need to join the tables and take the necessary columns.

However, you need to join the table user twice. When joining a table twice, at least one of them needs to have an alias. The query shown below uses aliases for all tables.

For example, you can do:

select
  q.id_request,
  c.name as caller_name,
  c.surname as caller_surname,
  r.name as receiver_name,
  r.surname as receiver_surname,
  c.category
from request q
join user c on c.id = q.id_caller
join user r on r.id = q.id_receiver -- joined user again with different alias
join category y on y.id = q.id_category
where q.id_caller = 1

CodePudding user response：

You just need two joins, one for the caller and another for the receiver.

select 
    id_request,
    uc.nm_user as "Caller_name",
    uc.nm_surname as "Caller_surname",
    rr.nm_user as "Receiver_name",
    rr.nm_surname as "Receiver_surname",
    c.nm_category    
from
    usr uc
join
    request rc on rc.id_caller = uc.id_user
join
    usr rr on rr.id_user = rc.id_receiver 
join
    category c on c.id_category = rc.id_category
where
    uc.id_user = 1

Here is the complete example https://www.db-fiddle.com/f/cA637bx33SFMeDtyUUNqsp/0

CodePudding user response：

You need to join the User table twice with request table -

SELECT R.Id_request,
       U1.Caller_name,
       U1.Caller_surname,
       U2.Receiver_name,
       U2.Receiver_surname,
       C.Category
  FROM Request R
  JOIN User U1 ON R.Id_caller = U1.Id
  JOIN User U2 ON R.Id_receiver = U2.Id
  JOIN Category C ON r.id_category = C.Id;