MySql - Select Query - Joins
I have two tables
User
ID UserName
1 Name1
2 Name2
3 Name3
4 Name4
5 Name5
Item
ID ItemName InsertedBy UpdatedBy
1 Item1 1 4
2 Item2 3 3
3 Item3 2 5
4 Item4 5 3
5 Item5 4 5
Resultant Table required
ID ItemName InsertedBy UpdatedBy
1 Item1 Name1 Name4
2 开发者_开发百科 Item2 Name3 Name3
3 Item3 Name2 Name5
4 Item4 Name5 Name3
5 Item5 Name4 Name5
How can this be achieved in single join query?
SELECt Id, ItemName, u1.UserName InsertedBy, u2.UserName UpdatedBy
FROM Item i, user u1, user u2
WHERE i.InsertedBy = u1.Id
AND i.UpdatedBy = u2.Id
If UpdatedBy can be null then query will be:
SELECt Id, ItemName, u1.UserName InsertedBy, u2.UserName UpdatedBy
FROM Item i JOIN user u1 ON (i.InsertedBy = u1.Id)
LEFT JOIN user u2 ON (i.UpdatedBy = u2.Id)
精彩评论