I have read the MSDN / Technet and understand there should be a column from the outer select inside the sub-select to join on, but with my table structure that is not exactly what I am after. I have a table with 2 columns, say
Type. There may be duplicate entries of
Name, and duplicate entries of
Type, but not a duplicate record of both
Type (think like a unique key across these 2 columns).
Sample fiddle: http://sqlfiddle.com/#!3/95f3b/3
DECLARE @food AS TABLE (FoodName NVARCHAR(200), FoodType NVARCHAR(200)) DECLARE @NEWfoods AS TABLE (FoodName NVARCHAR(200), FoodType NVARCHAR(200)) INSERT INTO @food (FoodName, FoodType) VALUES ('Apples', 'Fruit') ,('Avocado','Fruit') ,('Bananas', 'Fruit') ,('Mangos', 'Fruit') ,('Bread', 'Grain') ,('Cottage Cheese', 'Dairy') ,('Tacos', 'Meals') ,('Carrots', 'Vegetables') ,('Celery', 'Vegatables') INSERT INTO @NEWfoods ( FoodName, FoodType ) VALUES ('Avocado','Vegetables') ,('Apples','Fruit') ,('Salt','Preservative') ,('Turkey','Protein') ,('Bread','Grain') ,('Bread','Grain') ,('Tacos','Meals') SELECT f.FoodName ,f.FoodType FROM @food AS f WHERE NOT EXISTS ( SELECT * FROM @NEWfoods )
What I expect to happen is
Avocado:Vegetables will get inserted since the base table contains
Avocado:Fruit but not
Avocado:Vegetables (keeping in mind I am using both columns as a unique key),
Tacos:Meals will not be inserted at all since they already exist in the base table, and that
Salt:Preservative will be inserted, too.
So at the end of all of this it seems I am not using the right function here - that
NOT EXISTS in itself is not the right way. Coder's block! Any help would be appreciated. :)