MySQL 使用 WHERE 子句的多表连接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14830410/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Multiple Table Joins with WHERE clause
提问by Bhargav
I'm using Mysql and I'm having a difficult time trying to get the results from a SELECT query. I am having 3 tables. First table sections, second table section members and third table section member status(data in this table is static).
我正在使用 Mysql 并且我在尝试从 SELECT 查询中获取结果时遇到了困难。我有3张桌子。第一个表节、第二个表节成员和第三个表节成员状态（此表中的数据是静态的）。
select * from Sections;
select * from Sections;
| section_id | title | description | section_ownerid |
-------------------------------------------------------
| 1 | title1 | desc1 | 100 |
| 2 | title2 | desc2 | 100 |
| 3 | title3 | desc3 | 100 |
| 4 | title4 | desc4 | 100 |
| 5 | title5 | desc5 | 100 |
| 6 | title6 | desc6 | 100 |
select * from SectionMembers;
select * from SectionMembers;
| SectionMembers_id | section_id | status_code | memberid |
------------------------------------------------------------
| 1 | 1 | 10 | 200 |
| 2 | 1 | 20 | 300 |
| 3 | 2 | 30 | 200 |
| 4 | 2 | 10 | 300 |
| 5 | 3 | 30 | 200 |
| 6 | 4 | 20 | 200 |
select * from MemberStatus;
select * from MemberStatus;
| MemberStatus_id | status_code | status |
---------------------------------------------------
| 1 | 10 | PENDINGMEMBER |
| 2 | 20 | ACTIVEMEMBER |
| 3 | 30 | MEMBERREJECTED |
I used joins to get results as
我使用连接来获得结果
select distinct(a.section_id) as id,
a.title,
a.description,
c.status
from Sections a
left join SectionMembers b on a.section_id = b.section_id
inner join MemberStatus c on b.status_code = c.status_code
where (a.section_ownerid = 100 and b.memberid = 200)
or (a.section_ownerid = 100);
But I am not getting correct result. I want results as shown below:
但我没有得到正确的结果。我想要的结果如下所示：
| section_id | title | description | status |
------------------------------------------------------
| 1 | title1 | desc1 | PENDINGMEMBER |
| 2 | title2 | desc2 | ACTIVEMEMBER |
| 3 | title3 | desc3 | MEMBERREJECTED |
| 4 | title4 | desc4 | ACTIVEMEMBER |
| 5 | title5 | desc5 | NULL |
| 6 | title6 | desc6 | NULL |
回答by Taryn
It seems like the following query is what you need. Notice that the filter for memberid = 200has been moved to the join condition:
似乎以下查询正是您所需要的。请注意，过滤器memberid = 200已移至连接条件：
select s.section_id,
s.title,
s.description,
m.status
from Sections s
left join SectionMembers sm
on s.section_id = sm.section_id
and sm.memberid = 200
left join MemberStatus m
on sm.status_code = m.status_code
where s.section_ownerid = 100;
Note: while your desired result shows that section_id=2has a status of ActiveMemberthere is no way in your sample data to make this value link to section 2.
注意：虽然您想要的结果显示section_id=2状态为 ，ActiveMember但您的示例数据中无法将此值链接到第 2 部分。
This query gives the result:
此查询给出了结果：
| SECTION_ID | TITLE | DESCRIPTION | STATUS |
------------------------------------------------------
| 1 | title1 | desc1 | PendingMember |
| 2 | title2 | desc2 | MemberRejected |
| 3 | title3 | desc3 | MemberRejected |
| 4 | title4 | desc4 | ActiveMember |
| 5 | title5 | desc5 | (null) |
| 6 | title6 | desc6 | (null) |
回答by sensor
Here you go:
干得好：
SELECT s.section_id, s.title, s.description, ms.status
FROM Sections as s
LEFT JOIN SectionMembers as sm ON s.section_id = sm.section_id
LEFT JOIN MemberStatus as ms ON ms.status_code = sm.status_code
WHERE (s.section_ownerid = 100 and sm.memberid = 200)
OR (s.section_ownerid = 100)
GROUP BY section_id
Note:Updated my answer
注意：更新了我的答案
