CODEWITHSUNDEEP
sql-server-2005t-sql
Tim Schmelter
Tim Schmelter

Reputation: 460278

Select values that don't occur in a table

I'm sure this has been asked somewhere, but I found it difficult to search for.

If I want to get all records where a column value equals one in a list, I'd use the IN operator.

SELECT idSparePart, SparePartName 
  FROM tabSparePart 
  WHERE SparePartName IN (
      '1234-2043','1237-8026','1238-1036','1238-1039','1223-5172'
    )

Suppose this SELECT returns 4 rows although the list has 5 items. How can I select the value that does not occur in the table?

Thanks in advance.

Upvotes: 1

Views: 117

Answers (4)

John Sobolewski
John Sobolewski

Reputation: 4572

You could try something like this....

declare @test as table
(
    items varchar(50)
)

insert into @test
    values('1234-2043')

insert into @test
    values('1234-2043')

insert into @test
    values('1237-8026') 

-- the rest of the values --

select * from @test
    where items not in (
        select theItemId from SparePartName 
        )

for fun check this out... http://blogs.microsoft.co.il/blogs/itai/archive/2009/02/01/t-sql-split-function.aspx

It shows you how to take delimited data and return it from a table valued function as separate "rows"... which my make the process of creating the table to select from easier than inserting into a @table or doing a giant select union subquery.

Upvotes: 1

Tim Schmelter
Tim Schmelter

Reputation: 460278

As soon as you mentioned that i have to create a temp table, i remembered my Split-function.

Sorry for answering my own question, but this might be the the best/simplest way for me:

SELECT PartNames.Item
FROM   dbo.Split('1234-2043,1237-8026,1238-1036,1238-1039,1223-5172', ',') AS PartNames  
       LEFT JOIN tabSparePart ON tabSparePart.SparePartName = PartNames.Item
WHERE idSparePart IS NULL

My Split-function: Help with a sql search query using a comma delimitted parameter

Thank you all anyway.

Upvotes: 2

JohnD
JohnD

Reputation: 14787

Update: I misunderstood the question. I guess in that case I would select the values into a temp table, then select the values which are not in that table. Not ideal, I know -- the problem is that you need to get your list of part names to SQL Server somehow (either via IN or putting them in a temp table) but the semantics of IN don't do what you want.

Something like this:

CREATE TABLE tabSparePart
(
    SparePartName  nvarchar(50)
)
insert into tabSparePart values('1234-2043')


CREATE TABLE #tempSparePartName
(
    SparePartName  nvarchar(50)
)
insert into #tempSparePartName values('1234-2043')
insert into #tempSparePartName values('1238-1036')
insert into #tempSparePartName values('1237-8026') 

select * from #tempSparePartName
where SparePartName not in (select SparePartName from tabSparePart)

With output:

SparePartName
1238-1036
1237-8026

Original (wrong) answer: You can just use "not in":

SELECT * from tabSparePart WHERE SparePartName NOT in(
'1234-2043','1237-8026','1238-1036','1238-1039','1223-5172'
)

Upvotes: 1

Michał Powaga
Michał Powaga

Reputation: 23183

select t.* from (
    select '1234-2043' as sparePartName
    union select '1237-8026'
    union select '1238-1036'
    union select '1238-1039'
    union select '1223-5172'
) t
where not exists (
    select 1 from tabSparePart p WHERE p.SparePartName = t.sparePartName
)

Upvotes: 2

Related Questions