CODEWITHSUNDEEP
mysqljoin
Alekc
Alekc

Reputation: 4770

Mysql left join multiple columns from the same parent table

I'm having following problem with mysql query.

Given 2 tables

Person

Id | Name

1 | John Doe

2 | Jane Doe

3 | Pluto

Tickets

Id | Owner | Opener | AssignedTo

1 | 1 | 2 | 3

2 | 3 | 1 | 2

Owner, Opener, AssignedTo are Foreign keys linking to People Id

I need to make a query replacing Owner, Opener, AssignedTo columns with user names.

It's not a problem with just one column (it's enough to use left join), but I can't receive following output

select * from ....

1 | John Doe | Jane Doe | Pluto

2 | Pluto | John Doe | Jane Doe

Thanks in advance.

Upvotes: 0

Views: 3511

Answers (2)

Rolando Corratge Nieves
Rolando Corratge Nieves

Reputation: 1233

SELECT Tickets.Id , POwner.Name AS Owner, POpener.Name AS Opener, PAssignedTo.Name AS AssignedTo FROM Tickets JOIN Person AS POwner ON Owner = POwner.Id JOIN Person AS POpener ON Opener = POpener.Id JOIN Person AS PAssignedTo ON AssignedTo = PAssignedTo.Id

Upvotes: 0

xdazz
xdazz

Reputation: 160833

You could join Person multiple times.

SELECT t1.Id, t2.Name AS owner, t3.Name AS opener, t4.Name AS assignedTo
FROM Tickets t1
LEFT JOIN Person t2 ON t1.Owner = t2.Id
LEFT JOIN Person t3 ON t1.Opener = t3.Id
LEFT JOIN Person t4 ON t1.AssignedTo= t4.Id

Upvotes: 3

Related Questions