Mysql left join multiple columns from the same parent table

Question

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.

Thx Mr Anderson, but I was looking for an select statement, not update :) — Alekc
– Alekc, Commented Jul 9, 2012 at 14:06

xdazz · Accepted Answer · 2012-07-09 14:00:54Z

3

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

answered Jul 9, 2012 at 14:00

xdazz

161k38 gold badges255 silver badges278 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Marcus Adams Over a year ago

Why not use p1, p2, and p3 for aliases for the Person table instead of t2, t3, and t4?

Alekc Over a year ago

I suppose because i totally forgot about this possibility (shame on me)... Xdazz thanks, I'll accept your answer after 5 min (this timeout before accepting answer is kinda lame <.<)

Rolando Corratge Nieves · Accepted Answer · 2012-07-09 14:13:27Z

0

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

answered Jul 9, 2012 at 14:13

Rolando Corratge Nieves

1,2432 gold badges10 silver badges26 bronze badges

Collectives™ on Stack Overflow

Mysql left join multiple columns from the same parent table

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related