连接两个表而不返回多余的行 [英] Joining two tables without returning unwanted row
本文介绍了连接两个表而不返回多余的行的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我的表结构如下:
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 |
+---------+------------+---------------+-----------+--------------+
此刻我的SQL如下:
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
这将返回如下内容:
(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
这篇关于连接两个表而不返回多余的行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文