CODEWITHSUNDEEP
mysql
user1750957
user1750957

Reputation: 15

MySQL - How can I get Join data twice from one table

I have two MySQL tables, tbl_users and tbl_pagecontent. Inside tbl_pagecontent I have two columns 'UserCreated' and 'UserModified' which contain IDs of users inside tbl_users, and in tbl_users I have 'ID', 'Username',etc.

I guess my question is, how can I query a row, so after the page details (title, content, etc.), I also get the username that created and the username that modified (using corresponding IDs). I tried using a Left Join, but that only works for only one link.

Upvotes: 0

Views: 37

Answers (2)

Tun Zarni Kyaw
Tun Zarni Kyaw

Reputation: 2119

You can assign two different alias to the same table.

SELECT * 
FROM tbl_pagecontent p, tbl_users u1, tbl_users u2
WHERE p.usercreated = u1.userid
AND p.usermodified = u2.userid

(OR)

SELECT * 
FROM tbl_pagecontent p
LEFT JOIN tbl_users u1 ON p.usercreated = u1.userid 
LEFT JOIN tbl_users u2 ON p.usermodified = u2.userid

Upvotes: 0

jenson-button-event
jenson-button-event

Reputation: 18941

add two left joins to tbl_users, one from UserCreated & one from UserModified

select u1.Username as CreatedBy, u2.Username as ModifiedBy
from tbl_pagecontent 
left join tbl_users u1 on u1.id = tbl_pagecontent.UserCreated
left join tbl_users u2 on u2.id = tbl_pagecontent.UserModified

Upvotes: 1

Related Questions