0

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.

Tim Hanssen
  • 167
  • 1
  • 1
  • 9

3 Answers3

0

Try this:

SELECT *
FROM table2 
WHERE loc_id = 1
AND   atricle_id not in (SELECT article_id
                         FROM table1 
                         WHERE term_id = 20)
SMA
  • 36,381
  • 8
  • 49
  • 73
  • Thanks, but on this way I still get results if there are multiple records in table 1 with other termids. – Tim Hanssen Nov 13 '14 at 11:05
  • so what do you expect? only one row? – SMA Nov 13 '14 at 11:08
  • I need to have the records from table 2, that dont have term_id 20 in the other table. So if there is a match on 20, it should ignore the complete row from tabel 2. – Tim Hanssen Nov 13 '14 at 11:11
0

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
anoop
  • 3,229
  • 23
  • 35
0

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

Abhik Chakraborty
  • 44,654
  • 6
  • 52
  • 63