compare list values with with db ms sql 2008

[emmim44]

Hi there,

I need to compare a list values with a db column.

Example : list ='12,5,6,8'  and I need to know which of these values doesnt exist in db column values. How would I do that?

DbColumnKeys

1

10

100

1000

1001

1002

1003

1004

[Dan_Bracuk]

something like this should work

select list_item

from

(

<cfloop list="#yourlist#" index = "listitem">

select #listitem# list_item

union

</cfloop>

select 0 list_item

where 1 = 2

) temp left join from yourtable on list_item = somefield

where somefield is null

[emmim44]

I need this with pure sql ... How will i do that?

[Dan_Bracuk]

If you are going to use a cfquery, what I gave you should work.  If you are writing a stored procedure, the logic stays the same but the syntax for the loop changes.

[BKBK]

This works on MySQL:

SELECT dbCol

FROM (select 12 dbCol union select 5 dbCol2 union select 6 dbCol3 union select 8 dbCol4) someTBLAlias

WHERE dbCol NOT IN (SELECT dbCol FROM dbTBL)