CODEWITHSUNDEEP
mysql
Sheetal
Sheetal

Reputation: 21

Query to fetch the username for two columns from MYSQL database

I have two tables in mysql. Structure of tables are below.

USER: userid, username

Issue: id, issue_by (userid), issue_to (userid)

How can we fetch the username for both issue_by and issue_to for a particular Issue?

Upvotes: 2

Views: 200

Answers (2)

Dharmang
Dharmang

Reputation: 3028

You need to join to user table two times (yes, that is possible) :

SELECT i.id, u_by.username AS userby_name, u_to.username AS userto_name
FROM Issue AS i
LEFT JOIN `USER` AS u_by ON u_by.userid = i.issue_by
LEFT JOIN `USER` AS u_to ON u_to.userid = i.issue_to

In theory, you can join single table multiple times (there is no limit).

Upvotes: 1

Bohemian
Bohemian

Reputation: 424983

Try this:

select username
from user
join issue on user.userid in (issue_to, issue_from)
where id = ?

Upvotes: 0

Related Questions