如何选择其他表中没有链接记录的记录为空？

oipij1gg 于 2021-07-24 发布在 Java

关注(0)|答案(2)|浏览(241)

我有以下sql：

SELECT tournaments_blg.tournament_id_blg
FROM matches_blg  LEFT JOIN tournaments_blg ON tournaments_blg.tournament_id_blg = matches_blg.tournament_id_blg
WHERE matches_blg.match_id_op IS NULL
GROUP BY tournaments_blg.tournament_id_blg

这将选择所有 tournaments_blg.tournament_id_blg 其中任何一个 matches_blg.match_id_op 是 NULL .
我怎么只选择全部 tournaments_blg.tournament_id_blg 如果所有的 matches_blg.match_id_op 是 NULL ?

sql mysql

来源：https://stackoverflow.com/questions/63299702/how-to-select-records-where-no-linked-records-in-another-table-are-null

2条答案

按热度按时间

u2nhd7ah1#

SELECT tb.tournament_id_blg
FROM tournaments_blg tb
LEFT JOIN matches_blg mb ON tb.tournament_id_blg = mb.tournament_id_blg 
                        AND mb.match_id_op IS NOT NULL
WHERE mb.tournament_id_blg IS NULL

或

SELECT tb.tournament_id_blg
FROM tournaments_blg tb
WHERE NOT EXISTS ( SELECT NULL
                   FROM matches_blg mb 
                   WHERE tb.tournament_id_blg = mb.tournament_id_blg 
                     AND mb.match_id_op IS NOT NULL )

赞(0）回复(0）举报 2021-07-24

goucqfw62#

试试这个： SELECT tournaments_blg.tournament_id_blg FROM matches_blg WHERE matches_blg.match_id_op NOT IN (Select tournaments_blg from tournaments_blg )

赞(0）回复(0）举报 2021-07-24