我正在使用 Mysql，但在尝试从 SELECT 查询中获取结果时遇到了困难。 我有 3 张 table 。第一表段、第二表段成员、第三表段成员状态(本表数据为静态)。

从部分中选择 *；

| 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             |

从 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      |

从 MemberStatus 中选择 *；

| MemberStatus_id | status_code  | status         |
---------------------------------------------------
| 1               | 10           | PENDINGMEMBER  |
| 2               | 20           | ACTIVEMEMBER   |
| 3               | 30           | MEMBERREJECTED |

我使用连接来获得结果

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); 

但我没有得到正确的结果。我想要如下所示的结果:

| 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           |

最佳答案

看来下面的查询正是您所需要的。请注意，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;

注意:虽然您想要的结果显示 section_id=2 的状态为 ActiveMember，但您的样本数据中无法将此值链接到第 2 部分。

这个查询给出了结果:

| 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) |

关于mysql - 使用 WHERE 子句的多表连接，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/14830410/