CODEWITHSUNDEEP
sqlsql-server-2008
db1234
db1234

Reputation: 244

SQL Server 2008 - excluding rows

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!

Upvotes: 0

Views: 934

Answers (2)

Ian Kemp
Ian Kemp

Reputation: 29839

Use where not exists:

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

Upvotes: 2

Matt Wrock
Matt Wrock

Reputation: 6640

You could use:

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

Upvotes: 0

Related Questions