SQL Multiple SELECT Statements in one Query-CodePudding

I have the following table called approver:

id	subordinate_id	leader_id	main_leader_id
1	3	2	1
1	6	5	4

and the table called user:

id	full_name	phone
1	Paulo	2123
2	Marcos	3322
3	Leandro	43565
4	Any Moreira	23454
5	Maria S	43432
6	Jose Oliv	454567

I would like to make a query that brings the name of the users instead of the ids of the approver table, something like:

subordinate	leader	main_leader
Leandro	Marcos	Paulo
Jose Oliv	Maria S	Any Moreira

I tried with the following query but I only get one name at a time:

SELECT 
    U.full_name as subordinate
FROM user AS 
    U
INNER JOIN 
    approver as A 
ON 
    (A.subordinate_id = U.id);

enter image description here

how can i retrieve the user names from the approver table in the same way as i demonstrated in the example above?

CodePudding user response：

See if this works. You're only joining to the user table once so you are only decoding it once.

SELECT u1.full_name AS subordinate,
u2.full_name AS leader,
u3.full_name AS main_leader

FROM approver AS a
INNER JOIN user AS u1
ON a.subordinate_id = u1.id

INNER JOIN user AS u2
ON a.leader_id = u2.id

INNER JOIN user AS u3
ON a.main_leader_id = u3.id