加入两个表没有返回的行Joining two tables without returning unwanted row

- 此内容更新于:2014-12-30
主题:

原文:

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?

解决方案:
原文:
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
Federico A. Ramponi的回复:我猜有两个外键的问题。creatorid,问题。assignedid给用户。userid,每个表中没有什么可以为空。考虑到这些场所,有什么区别使用左连接和内部连接?是一个更自然,或者更快,比其他吗?

(原文:I guess that there are two foreign keys from issues.creatorid, issues.assignedid to users.userid, and that nothing can be null in each table. Given these premises, is there any difference between using left joins and inner joins? Is one more natural, or faster, than the other?)

解决方案:
在一般知识方面,我们杰出的网站创始人写的一个非常好的博客文章在这个问题上,我发现自己指了一遍又一遍。 SQL连接的视觉解释
原文:

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

Ross的回复:谢谢你马克,我# 39;你给一个一个读!

(原文:Thanks Mark, I'll give that one a read!)

解决方案:
用这个:
原文:

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
解决方案:
不确定的或不需要括号。
原文:
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.

Ross的回复:对不起,这个仍然返回问题# 2双排。

(原文:Sorry, this still returns a double row for issue #2.)

解决方案:
这工作吗? 选择 i.issueid, i.assignedid, u1。real_name assigned_name, i.creatorid, u2。real_name作为creator_name 从用户u1 内部加入问题我u1。用户id = i.assignedid 内部加入用户u2乐队u2。用户id = i.creatorid ORDER BY i.issueid
原文:

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

解决方案:
选择 i.issueid, i.assignedid, a.real_name, i.creatorid, c.real_name 从 问题我 内部加入用户c 在c。用户id = i.creatorid 内部加入用户 在一个。用户id = i.assignedid Order by 我。issueid ASC
原文:

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