How to return records from one table that don't exist in another 1

[AndyHorn]

I want to show all records from my stock table that are members of a specific parent category but not members of a specific sub category. The stock table contains the parent category field but any sub categories the item is a member of are stored in a seperate table.

There will only ever be one entry for a stock item in the stock table but there could be any number of the same stock item listed in the sub category table just with different sub categories.

The stock table structure: Sku, CategoryCode
The stock sub category table structure: Sku, SubCategoryCode

An example of the data.

Stock.Sku, Stock.CategoryCode
abc,LW
def,OTT
ghi,LW
jkl,LW
mno,LW

StockSubCategory.Sku, StockSubCategory.SubCategoryCode

jkl,LWX
mno,LWX

I want my result to return all stock items that have a Stock.CategoryCode of 'LW' and exclude those that have an entry in the StockSubCategory table with a SubCategoryCode of 'LWX'. So the results would be abc & ghi.

I've tried a number of things the latest of which is:

select sku
from stock
where stock.categorycode = 'LW' and not exists
(SELECT sku FROM StockSubCategory WHERE StockSubCategory.SubCategoryCode = 'LWX')

The result set of this is blank.

If hope all that makes sense. Any ideas?
Thanks in advance.

 

[RiverGuy]

You need more criteria in your subquery

select sku
from stock
where stock.categorycode = 'LW' and not exists
(SELECT sku FROM StockSubCategory WHERE StockSubCategory.SubCategoryCode = 'LWX' [b]AND stock.sku = StockSubCategory.sku[/b])

 

[bborissov]

SELECT  sku
        from stock
WHERE stock.categorycode = 'LW' and
      stock.Sku NOT IN (SELECT sku
                               FROM StockSubCategory
                         WHERE SubCategoryCod = 'LWX')

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[AndyHorn]

Thanks RiverGuy, it worked a treat!