SQL query to join and verify if there's no corresponding rows

Question

I'm currently doing this query, however, I'm always getting a result where there should be none, because there's a corresponding row.

select `likes`.*, `likeshistory`.`like_id`, `likeshistory`.`instagram_id` from `likes` join `likeshistory` on `likes`.`id` = `likeshistory`.`like_id` where (`finished` = '0' and `active` = '1' and `banned` = '0') and `instagram_id` not in ('2') limit 1

Basically I want to select one "likes" that has no "likeshistory" for (instagram_id = 2)

likeshistory structure

Thank you! William

Can you please post sample data and structure? – Ted at ORCL.Pro Sep 28 '17 at 19:48 — Ted at ORCL.Pro, Sep 28 '17 at 19:48

score 0 · Answer 1 · answered Sep 28 '17 at 19:48

You can use not exists. The purpose of the query is clearer:

select `likes`.*, `likeshistory`.`like_id`, `likeshistory`.`instagram_id`
from `likes` l
where not exists (select 1
                  from `likeshistory` lh
                  where l.id = lh.`like_id` and
                        (`finished` = '0' and `active` = '1' and `banned` = '0') and lh.instagram_id not in ('2')
                 );

If any of the conditions in the subquery (except the correlation clause) are on likes, then those should be in the outer query.

SQL query to join and verify if there's no corresponding rows

1 Answers1