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.

see http://stackoverflow.com/questions/2334712/sql-server-update-from-select — T I, Jul 09 '12 at 13:59
Thx Mr Anderson, but I was looking for an select statement, not update :) — Alekc, Jul 09 '12 at 14:06

score 3 · Accepted Answer · answered Jul 09 '12 at 14:00

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 09 '12 at 14:00

xdazz

158,678
38
247
274

Why not use p1, p2, and p3 for aliases for the Person table instead of t2, t3, and t4? – Marcus Adams Jul 09 '12 at 14:05
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 <.<) – Alekc Jul 09 '12 at 14:08

score 0 · Answer 2 · answered Jul 09 '12 at 14:13

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 09 '12 at 14:13

Rolando Corratge Nieves

1,233
2
10
25

Mysql left join multiple columns from the same parent table

2 Answers2