How to call different data from one column via intermediary table?-CodePudding

At first I have to say that I am an absolute SQL newbie.

I have two SQL tables. The second one is an intermediary m:n table.

Table: people

id	name
1	Yoda
2	Anakin
3	Luke
4	Obi-Wan

Table: master-padawan_relation

master_id	padawan_id
1	3
4	2

master-padawan_relation.master_id and master-padawan_relation.padawan_id both refer to people.id.

Now I want a query that shows the following data:

Master	Padawan
Yoda	Luke

My query looks currently like this but it lacks the integration of the Masters name:

SELECT
    people.name AS Padawan
FROM
    people
LEFT JOIN 
    master-padawan_relation ON master-padawan_relation.padawan_id = people.id
WHERE 
    people.id = 3

I don't know how to get that Masters name in there, because both names are in the same column (people.name).

I also managed to get the masters id, but I really need that name.

Appreciate every help! Thank you :)

CodePudding user response：

To get the master's name, you will need to join the people table again and use the master_id in the master-padawan_relation table to look up the master's name

SELECT
master.name AS Master,
padawan.name AS Padawan
FROM people AS padawan
LEFT JOIN master-padawan_relation
ON master-padawan_relation.padawan_id = padawan.id
LEFT JOIN people AS master
ON master.id = master-padawan_relation.master_id
WHERE padawan.id = 3