How to identify entries where a data does not exist in a table with a condition

I would like to pull all data where the Article exist in Table 1 but not in Table 2 when the CnTy is PB00. So if the article exist in Table 2 for other CnTy that is NOT PB00, I still want it on the report. I’ve been using a where CnTY != ‘PB00’ and it just filters out PB00 instead of giving me Articles that do NOT contain ‘PB00’. I don’t think I can use a left join because of the added condition.

This is what I have:

select* from T1
join T2 on T1.Article = T2.Article
WHERE T2.CNTY != 'PB00'

Sample data for Table 2:

In this particular example, this entire article shouldn’t be included because of PB00 records. However if the article only contained ZOID type then it needs to be identified

Answer

Use not exists:

select t1.*
from T1
where not exists (select 1
                  from t2
                  where T2.Article = T1.Article and
                        T2.CNTY = 'PB00'
                 );

Advertisement

Answer