CODEWITHSUNDEEP
mysql
Gavin Simpson
Gavin Simpson

Reputation: 2822

Using multiple columns in a join on same field in second table

Consider the following :

**Table 1 - record**
id (int primary key),
addedby (int),
editedby (int)

**Table 2 - users**
id (int primary),
name (shorttext)

**Sample Records**
record
0 1 1
1 1 2

users
1 user1
2 user2

What I need is so do a join to to be able to show the following :

record.id, users.addedby, users.editedby

I tried, amongst others, the following :

select record.id, users.name, users.name from record left join users on record.addedby=users.id left join users on record.editedby=users.id

However, it's not even logical that that will work, so I am a bit stuck.

Any help would be much appreciated. Thank you.

Upvotes: 0

Views: 25

Answers (2)

Jens
Jens

Reputation: 69440

Use aliasses:

select record.id, users1.name, users2.name 
from record 
left join users users1 on record.addedby=users1.id 
left join users users2 on record.editedby=users2.id

Upvotes: 1

pala_
pala_

Reputation: 9010

Just join the same table twice. Nothing unusual. You just have to alias the tables to be able to refer to them independently.

select r.id, u1.name added, u2.name editor
  from record r
    inner join user u1
      on r.addedby = u1.id
    inner join user u2
      on r.editedby = u2.id

heres a demo

Upvotes: 2

Related Questions