Skip navigation
emmim44
emmim44
Currently Being Moderated

compare list values with with db ms sql 2008

Jun 13, 2012 5:42 AM

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

721 Views   4 Replies   Latest reply: BKBK, Jul 2, 2012 2:40 AM
 
Replies
  • Dan Bracuk
    Currently Being Moderated
    1. Dan Bracuk,
    Jun 13, 2012 9:23 AM   in reply to emmim44
    Report

    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

     
    |
    Mark as:
  • emmim44
    Currently Being Moderated
    2. emmim44,
    Jun 14, 2012 1:07 AM   in reply to Dan Bracuk
    Report

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

     
    |
    Mark as:
  • Dan Bracuk
    Currently Being Moderated
    3. Dan Bracuk,
    Jun 14, 2012 5:27 AM   in reply to emmim44
    Report

    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.

     
    |
    Mark as:
  • BKBK
    Currently Being Moderated
    4. BKBK,
    Jul 2, 2012 2:40 AM   in reply to emmim44
    Report

    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)

     
    |
    Mark as:
Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points