子查询在联接时返回多于1行

Question

类结构inheritancetype.joined jpa

我想在箱子上加入不同的桌子。

盲DB

╔════╦══════════════╦════════════╦
║ id ║ account_id   ║ type       ║ 
╠════╬══════════════╬════════════╬
║  1 ║ 1            ║  POST      ║
║  2 ║ 2            ║  COMMENT   ║
║  3 ║ 1            ║  POST      ║
║  4 ║ 2            ║  POST      ║
║  5 ║ 2            ║  BOARD     ║
╚════╩══════════════╩════════════╩

盲DB

╔════╦══════════════╦
║ id ║ post_id      ║
╠════╬══════════════╬
║  1 ║ 1            ║
║  3 ║ 3            ║
║  4 ║ 2            ║
╚════╩══════════════╩

post DB

╔════╦══════════════╦════════════╦
║ id ║ is_blind     ║ account_id ║
╠════╬══════════════╬════════════╬
║  1 ║ 1            ║  1         ║
║  2 ║ 0            ║  1         ║
║  3 ║ 0            ║  2         ║
╚════╩══════════════╩════════════╩

盲人有两种身份。blind和blind waiting在发布或评论盲目时，创建了盲对象，6小时后，post is_blind或comment is_blind列变为真，这样用户就不能观看该内容。

类型为“post”时:盲-> post_blind -> post

并选择post is_blind列为false并选择blind account_id = :account_id

SELECT *
FROM blind b
         LEFT JOIN post_blind pb ON b.id = pb.id
         LEFT JOIN comment_blind cb ON b.id = cb.id
WHERE b.account_id = 2
          and (b.type = 'POST' AND (select is_blind
                                    from post p
                                    right join post_blind pb on p.id = pb.post_id) is true)
   OR (b.type = 'COMMENT' AND (select is_blind
                               from comment c
                                right join comment_blind cb on c.id = cb.comment_id) is true);

如果帐户有一个盲目的帖子和一个盲目的评论，它就会起作用。

但是，如果帐户有一个以上的盲文，Subquery will return more than one row返回。

我已经看过其他的问题和答案，但仍然不明白。有人能帮我解决这个问题吗？

Answer 1

使用存在关键字

SELECT *
FROM blind b
LEFT JOIN post_blind pb ON b.id = pb.id
LEFT JOIN comment_blind cb ON b.id = cb.id
WHERE b.account_id = 2
     and (b.type = 'POST' and EXISTS
        (select is_blind
        from post p
        right join post_blind pb on p.id = pb.post_id) is true)
            OR (b.type = 'COMMENT' 
                and EXISTS (select is_blind
                            from comment c
                            right join comment_blind cb on c.id = cb.comment_id)
                );