Subquery returns more than 1 row when join

user_16996269 0
Viewed 62

class structure inheritancetype.joined jpa
I want to join different table on case.

blind DB

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

post_blind 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 has two status. blind and blind waiting When post or comment blinded, blind object created and after 6 hours later, post is_blind or comment is_blind column turns true so users can't watch that content.

when type is 'POST' : blind -> post_blind -> post
and select blind that post is_blind column is false and 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);

It works if account has one blind post and one blind comment.
However, if the account has more than one blind post, Subquery will return more than one row return.

I have already looked at other question and answer but still don't understand. anyone can help me solve this problem?

hibernate mysql sql database java
1 Answers

use EXISTS keyword

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