如果存在其他2个表中的匹配项，则获取所有表值

问题描述

我有一个表频道".

channelId
a
b
c
d

表格视频"

videoId | channelId
1       | a
2       | b
3       | c
4       | e

表注释"

commentID | videoID | videoID_channelID
xx        | 1       | a
yy        | 2       | b
zz        | 5       | e
tt        | 6       | f

键是:

• channel.channelId = video.channelId = comment.videoID_channelID
• video.videoId = comment.videoID

我需要:

• 所有带有至少1个视频和1条评论的频道
• 所有带有至少1个频道和1条评论的视频
• 带有视频和频道的所有评论

因此，我想执行3条SQL语句，每个表引用另一个2条语句.

So I want to do 3 SQL statements, one for each table that references the other 2.

我尝试了两次内部联接( https://www.sqlitetutorial.net/sqlite-inner-join/)，但似乎会返回所有适合的组合，而不是:

I tried it with a double inner-join (https://www.sqlitetutorial.net/sqlite-inner-join/) but it seems to return all combinations that fit rather than:

channelId
a
b

videoId | channelId
1       | a
2       | b

commentID | videoID | videoID_channelID
xx        | 1       | a
yy        | 2       | b

到目前为止，我的代码可以获取所有带有至少1个视频和1条评论的频道:

My code so far to get all channels with at least 1 video and 1 comment:

SELECT
        channel.channelId
FROM
    channel
    INNER JOIN video ON video.channelId = channel.channelId
    INNER JOIN comment ON comment.videoID_channelID = video.channelId

推荐答案

您可以通过连接所有3个表的相同查询获得所需的所有结果，但是对于每种情况，请选择不同的列:

You can get all the results that you want with the same query that joins all 3 tables, but for each case select different columns:

SELECT c.channelId
FROM channel c
INNER JOIN video v ON v.channelId = c.channelId
INNER JOIN comment cm ON cm.videoID_channelID = v.channelId;

SELECT v.videoID, c.channelId
FROM channel c
INNER JOIN video v ON v.channelId = c.channelId
INNER JOIN comment cm ON cm.videoID_channelID = v.channelId;

SELECT cm.commentID, v.videoID, c.channelId
FROM channel c
INNER JOIN video v ON v.channelId = c.channelId
INNER JOIN comment cm ON cm.videoID_channelID = v.channelId;

如果实际数据中有重复项，则可能必须在每个SELECT之后添加DISTINCT.
请参见演示.
结果:

You may have to add DISTINCT after each SELECT if you get duplicates in your actual data.
See the demo.
Results:

| channelId |
| --------- |
| a         |
| b         |


| videoID | channelId |
| ------- | --------- |
| 1       | a         |
| 2       | b         |


| commentID | videoID | channelId |
| --------- | ------- | --------- |
| xx        | 1       | a         |
| yy        | 2       | b         |

这篇关于如果存在其他2个表中的匹配项，则获取所有表值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！