Skip to main content
E
emmim44
Inspiring
June 13, 2012
Question

compare list values with with db ms sql 2008

  • June 13, 2012
  • 2 replies
  • 1618 views

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

This topic has been closed for replies.

2 replies

BKBK
Community Expert
BKBKCommunity Expert
Community Expert
July 2, 2012

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)

D
Dan_Bracuk
Inspiring
June 13, 2012

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

E
emmim44Author
Inspiring
June 14, 2012

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

D
Dan_Bracuk
Inspiring
June 14, 2012

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.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices