Finding Records that don't match within a field that do match

[trulyblessed]

I'm trying to find records that for a certain NDC, their quantities do not equal what is on another table. So, for table1, I could have:
NDC Qty
123456789 1
123456789 6
123456789 12

I'm linking to table2 on NDC and table 2 could have:
NDC Qty
123456789 6
123456789 12

I want the query to return all of the fields from table1, for NDC 123456789 where the qty was 1, but what I'm finding is happening in the way that I'm doing it, is it's returning a record for all three of the quantities because there's a record where qty of 1 didn't equal 6 or 12 and qty of 6 didn't equal 12 and where qty 12 didn't equal 6.

How can I write the query so that it only returns rows from table 1 where for that NDC, the quantity wasn't listed in table2 at all??

Thanks,
Kim

 

[swampBoogie]

select * from table1 t1
where qty not in (
select qty from table2 t2
where t2.ndc = t1.ndc)

 

[100dtl]

Select * from table1 where id not in(select id from table2 where qty = 1)

 

[trulyblessed]

Swampboogie - This code seemed to work pretty good. Thank you. I'm just running into one problem and I'm not sure if it's because of the syntax or something else. I hope you can help me figure this out.

It appears that when one NDC has multiple quantities that cannot be found in table 2, it's only outputting 1 of the multiple records. For example:
Table 1:
NDC Qty
123456789 1
123456789 2

Table 2:
NDC Qty
123456789 4
123456789 6

When I use the syntax supplied, I'm only getting back the NDC 123456789 with the qty of 2, when it should be giving me back both records. Do you see what I mean?

Any help would be greatly appreciated.

Thanks,
Kim

 

[trulyblessed]

Never Mind! I just realized in the one particular query where I was having that problem, I was missing the part of the syntax where t2.ndc = t1.ndc.

Sorry about that.

Thank you, it seems to work great!
Kim