MySQL Joins

Question

My table structure looks like this:

      tbl.users                       tbl.issues
+--------+-----------+   +---------+------------+-----------+
| userid | real_name |   | issueid | assignedid | creatorid |
+--------+-----------+   +---------+------------+-----------+
|   1    |   test_1  |   |    1    |     1      |     1     |
|   2    |   test_2  |   |    2    |     1      |     2     |
+--------+-----------+   +---------+------------+-----------+

Basically I want to write a query that will end in a results table looking like this:

                           (results table)
+---------+------------+---------------+-----------+--------------+
| issueid | assignedid | assigned_name | creatorid | creator_name |
+---------+------------+---------------+-----------+--------------+
|    1    |     1      |    test_1     |    1      |    test_1    |
|    2    |     1      |    test_1     |    2      |    test_2    |
+---------+------------+---------------+-----------+--------------+

My SQL looks like this at the moment:

SELECT 
`issues`.`issueid`,
`issues`.`creatorid`,
`issues`.`assignedid`,
`users`.`real_name`
FROM `issues`
JOIN `users` ON ( `users`.`userid` = `issues`.`creatorid` )
OR (`users`.`userid` = `issues`.`assignedid`)
ORDER BY `issueid` ASC
LIMIT 0 , 30

This returns something like this:

                (results table)
+---------+------------+-----------+-----------+
| issueid | assignedid | creatorid | real_name |
+---------+------------+-----------+-----------+
|    1    |     1      |     1     |   test_1  |
|    2    |     1      |     2     |   test_1  |
|    2    |     1      |     2     |   test_2  |
+---------+------------+-----------+-----------+

Can anyone help me get to the desired results table?

Thanks, Ross

Answer 1

SELECT DISTINCT (i.issueid, i.creatorid, i.assignedid, u.real_name)
FROM issues i, users u
WHERE u.userid = i.creatorid OR u.userid = assignedid
ORDER BY i.issueid ASC
LIMIT 0 , 30

Not sure if the parenthesis are needed or not.

Answer 2

SELECT IssueID,AssignedID,CreatorID,AssignedUser.real_name As AssignedName,CreatorUser.real_name As CreatorName
From Issues
LEFT JOIN Users As AssignedUser
On Issues.AssignedID = AssignedUser.UserID
LEFT JOIN Users As CreatorUser
On Issues.CreatorID = CreatorUser.UserID
ORDER BY `issueid` ASC
LIMIT 0 , 30

Answer 3

Use this:

SELECT 
`issues`.`issueid`,
`issues`.`creatorid`,
`creator`.`real_name`,
`issues`.`assignedid`,
`assigned`.`real_name`
FROM `issues` i
INNER JOIN `users` creator ON ( `creator`.`userid` = `issues`.`creatorid` )
INNER JOIN `users` assigned ON (`assigned`.`userid` = `issues`.`assignedid`)
ORDER BY `issueid` ASC
LIMIT 0 , 30

Answer 4

Does this work?

SELECT i.issueid, i.assignedid, u1.real_name as assigned_name, i.creatorid, u2.real_name as creator_name FROM users u1 INNER JOIN issues i ON u1.userid = i.assignedid INNER JOIN users u2 ON u2.userid = i.creatorid ORDER BY i.issueid

Answer 5

SELECT
i.issueid,
i.assignedid,
a.real_name,
i.creatorid,
c.real_name
FROM
issues i
INNER JOIN users c
ON c.userid = i.creatorid
INNER JOIN users a
ON a.userid = i.assignedid
ORDER BY
i.issueid ASC

Answer 6

On the general knowledge front, our illustrious site founder wrote a very nice blog article on this subject which I find myself referring to over and over again.

Visual Explanation of SQL Joins