select *
from table1
where itemnumber not in ( select itemnumber from table2 )
You could further qualify for a specific site within the sub-select from table 2...
not in ( select itemnumber from table2 where siteid = 3)
>I'm having trouble coming up with the right way to do this. Here is my problem:
>
>2 tables. The first table is a transaction table that has the following fields:
>
>ItemNumber
>datetime
>qty
>siteid
>
>2nd Table
>
>ItemNumber
>UnitCost
>siteid
>
>The 2nd table is a price table for the current site (siteid). During day to day use, the cost table is not used, but items are added to the transaction table. I need to be able to create a query/report that lists items in the transaction table that do not have a record in the cost table.
>
>I'm sure there is a way to create a view on this, but right now, it's not coming to me.
>
>Any help appreciated.
>Kirk
>
kkelly@cqisol.com