Select exclude if one row matches

Question

I'm looking for a fast way to search in a table by excluding results in a join.

Two simpelfied tabels:

table 1
- article_id
- term_id

table 2
- article_id
- loc_id

In table 1 there can be multiple rows for the same article_id, it can be linked to multiple terms. I'm looking for a select query to get all the results from table 2, with loc_id 1 that dont have a row in table 1 with term_id 20.

The two tables are joind on there article_ids ofc.

If i use a normale join, and then set a where on term_id != 20, i still get the results if the article is linked to term_id 19.

Answer 1

Try this:

SELECT *
FROM table2 
WHERE loc_id = 1
AND   atricle_id not in (SELECT article_id
                         FROM table1 
                         WHERE term_id = 20)

Answer 2

Try as below

select * from table1 as t1  join  table2 as t2
on t1.article_id=t2.article_id
where t2.loc_id = 1 and t1.term_id <> 20

Answer 3

You can use not exists something as

select * from table2 t2
where loc_id = 1
and not exists
(
  select 1 from table1 t1
  where 
  t1.term_id = 20
  and t1.article_id = t2.article_id

)

Here is a demo but with different data set