SQL Server 2008 - excluding rows

Question

Hi,

I have the following query

DECLARE @ProductIdsToExclude nvarchar(max)

SET @ProductIdsToExclude = '49506541-4CE2-40AC-812A-7AB262E6F0B0,49506541-4ce2-40ac-812a-7ab262e6f0b0'

I'm then using this function to parse @ProductIdsToExclude to a temp table:

CREATE TABLE

#TempProductIdsToExclude (ProductId uniqueidentifier)

INSERT

#TempProductIdsToExclude (ProductId)

SELECT

t.txt_value

FROM

dbo.fn_ParseText2Table(@ProductIdsToExclude,',') as t

I'm then using this query to pull out all Products:

SELECT * FROM Products

My question is - how can I get the query to exclude all results where the ProductId in the Products table is contained within #TempProductIdsToExclude

thanks!

Answer 1

You could use:

select * from products where productId not in (select productId from ) #TempProductIdsToExclude)

Answer 2

Use where not exists:

select *
  from Products prod with (nolock)
 where not exists (select 1
                     from #TempProductIdsToExclude temp
                    where temp.ProductId = prod.ProductId)