mysql - 从多个表中选择 using in statement 并显示所有内容，无论结果如何

Question

我有 3 个表。

第一个表“projects”是这样的:

id  |   title
28  |   pr28
31  |   pr31
65  |   pr65
74  |   pr74

第二个表“stages”是这样的:

pr_id   |   id  |   t_id|   title   |   comments
28      |   65  |   1   |   st1     |   bla
28      |   86  |   3   |   st3     |   blaaaa
31      |   132 |   1   |   st1     |   blabla
65      |   189 |   1   |   st1     |   bli
65      |   190 |   2   |   st2     |   blibli
65      |   224 |   3   |   st3     |   bliblib
74      |   319 |   3   |   st3     |   blibli
74      |   333 |   5   |   st5     |   blibli

第三张表“stad_ids”是这样的:

id  |   title
1   |   st1
2   |   st2
3   |   st3
4   |   st4
5   |   st5

我想从第一个表中找到 t_id 为 1 或 2 的项目。这里没问题:

 SELECT a.id, st.t_id, st.title, st.comments
 FROM  `stages` AS st
 JOIN `projects` AS a ON a.id = st.pr_id
 and a.id IN (SELECT a.id FROM stages as st
 JOIN `projects` AS a ON a.id = st.pr_id
 AND st.t_id in (1,2))
 order by a.id

这个输出:

id  |   t_id    |   title   |   comments
28  |   1       |   st1     |   bla
31  |   1       |   st1     |   blabla
65  |   1       |   st1     |   bli
65  |   2       |   st2     |   blibli

现在，我想显示 t_id 为 1 或 2 的项目中 id 为 2 或 3 的阶段。

SELECT a.id, st.t_id, st.title, st.comments FROM  `stages` AS st JOIN
`projects` AS a ON a.id = st.pr_id and st.t_id in (2,3) and a.id IN
(SELECT a.id FROM stages as st JOIN `projects` AS a ON a.id = st.pr_id
AND st.t_id in (1,2))
order by a.id

这个输出:

id  |   t_id    |   title   |   comments
28  |   3       |   st3     |   blaaaa
65  |   2       |   st2     |   blibli
65  |   3       |   st3     |   bliblib

相反，我想要以下输出:

id  |   t_id    |   title   |   comments
28  |   2       |   st2     |   "NO VALUE"
28  |   3       |   st3     |   blaaaa
65  |   2       |   st2     |   blibli
65  |   3       |   st3     |   bliblib

我试过使用第三张表:

SELECT distinct a.id, ids.id AS t_id, ids.title, st.comments
FROM  `stages` AS st
JOIN `stad_ids` AS ids ON ids.id in (2,3)
JOIN `projects` AS a ON a.id = st.pr_id
and st.t_id in (2,3)
and a.id IN (SELECT a.id FROM stages as st
JOIN `projects` AS a ON a.id = st.pr_id
AND st.t_id in (1,2)) 
order by a.id

但输出重复注释。

id  |   t_id    |   title   |   comments
28  |   2       |   st2     |   blaaaa
28  |   3       |   st3     |   blaaaa
65  |   2       |   st2     |   blibli
65  |   3       |   st3     |   bliblib

有什么建议吗？

Answer 1

找到目标项目并使用 2 行阶段 ID(2 和 3)进行交叉连接，以创建项目 ID、阶段 ID/标题的所有可能组合。使用左联接引入评论。

SELECT a.id, ids.id as t_id, ids.title, COALESCE(st.comments, '"NO VALUE"') as comments
FROM `stad_ids` as ids
CROSS JOIN `projects` as a
LEFT JOIN `stages` as st ON ids.id = st.t_id and a.id = st.pr_id
WHERE ids.id in (2, 3) AND
      a.id IN
    (SELECT a.id FROM stages as st JOIN `projects` AS a ON a.id = st.pr_id
         AND st.t_id in (1,2)) AND
      EXISTS (SELECT 1 FROM `stages` as st1 WHERE st1.pr_id = a.id AND st1.t_id in (2, 3))
ORDER BY a.id,  ids.id;

Demo