General SQL question - set operations

[stiley]

Hello, I am fairly new to SQL and have a question that I'd like to pose.

I have a list of IDs an I would like to find which IDs in that list do not exist in a particular table. It is simple to find the ones that exist in the table, but I am not sure how to go the other way.

For example say I am give the list {A,B,C,D,E,F}

The query
"Select ID from SOME_TABLE where ID IN (A,B,C,D,E,F)" returns C,D,F

I'd like to determine if it is possible to write a query that would give me the remaining unmatched IDs (A,B,E)

It is simple when the list only contains a few values, but when the list contains 100 or so IDs the manual way is not fun.

I can not create a table or a temp table to do this.

Any input is appreciated

Thanks
Sean

 

[bborissov]

Add this list in Table variable and the LEFT join the main table:

DECLARE @Test TABLE (Ids CHAR(1))
INSERT INTO @Test VALUES ('A')
INSERT INTO @Test VALUES ('B')
INSERT INTO @Test VALUES ('C')
INSERT INTO @Test VALUES ('D')
INSERT INTO @Test VALUES ('E')
INSERT INTO @Test VALUES ('F')

SELECT Test.IDs 
FROM @Test Test
LEFT JOIN Some_Table ON Test.Ids = Some_Table.Id
WHERE Some_Table.Id IS NULL

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[stiley]

Do I need to create a function or something? When i try this I get an error.

 

[bborissov]

What error?
And also how do you pass the list?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[stiley]

Sorry Using Oracle.

I figured it as follows

SELECT * FROM (
select 'A' as id from dual union
select 'B' as id from dual union
select 'C' as id from dual union
select 'D' as id from dual union
select 'E' as id from dual) temp
WHERE NOT EXISTS(SELECT * FROM SOME_TABLE a WHERE a.ID=temp.ID);

 

[bborissov]

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[MDXer]

There are a number of forums for Oracle which you would probably get better answers from due to the sometime not so subtle differences between the 2.

DBMS Packages

will take you to a listing of the available forums

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[stiley]

Thank you for your suggestions. I'll be sure to post to the appropriate spot next time

Sean