0

I know how to check for the value of a table column inside a variable list, like so:

<cfquery datasource="test_sql" name="get">
    Select a
    From b
    Where c In  
    (
        <cfqueryparam cfsqltype="cf_sql_varchar" list="true" value="#d#">
    )
</cfquery>

But how do I reverse the where clause? I've tried this which hasn't worked:

<cfquery datasource="test_sql" name="get">
    Select a
    From b
    Where <cfqueryparam cfsqltype="cf_sql_varchar" value="#c#"> In (d)
</cfquery>

This then looks for any match within the list stored in column d that has the value c.

Eg.

C = 12345

Column D - 4 Rows
12344,12345,12346 --- Match (List Item 2)
12323,12327,12375 --- No Match
12312,12341,12345 --- Match (List Item 3)
12128,12232,12345 --- Match (List Item 3)

The record count should then be 3 as there are a total of 3 matches where the value is present within the list. However, when I run this query it does not work.

I'd assume many people have stumbled upon this minor problem before and know the solution. Any help would be appreciated!

Leigh
  • 28,765
  • 10
  • 55
  • 103
Banny
  • 811
  • 4
  • 13
  • 36
  • What do you mean by 'it does not work'? Do you get an error? Do you not get the correct results? Also, you should rework the database structure. You should not be storing comma delimited lists in a column in a DB. Each item should be in its own row in a separate table. – Scott Stroz Mar 21 '14 at 11:45
  • IMO, the best thing you could do is normalize the data. This should be a very simple query. While the LIKE technique works, frankly it is convoluted and the sql more complex than it would be if the data were properly normalized. Not to mention it is [bad for data integrity and performance](http://stackoverflow.com/a/3653574/104223). – Leigh Mar 21 '14 at 18:29
  • The first query, which you imply is good, doesn't look too good to me. If d is the column name, you will get an error for an undefined ColdFusion variable. – Dan Bracuk Mar 21 '14 at 18:53

2 Answers2

2

This query logic should work

where d = <cfqueryparam cfsqltype="cf_sql_varchar" value="#c#"> 
--- c is only element
or d like <cfqueryparam cfsqltype="cf_sql_varchar" value="#c#,%"> 
--- c is first element
or d like <cfqueryparam cfsqltype="cf_sql_varchar" value="%,#c#,%"> 
--- c is in the middle somewere
or d like <cfqueryparam cfsqltype="cf_sql_varchar" value="%,#c#"> 
--- c is last element
Dan Bracuk
  • 20,699
  • 4
  • 26
  • 43
  • Although a pain to implement, this is a good workaround for the issue. Marked as a correct answer. – Banny Mar 24 '14 at 16:41
-4

It doesn't know the variable c before the query is compiled. If you remove the cfqueryparam tag from the variable that is replacing the column name, it will work.

<cfquery datasource="test_sql" name="get">
  Select a
  From b
  Where #c# In (<cfqueryparam cfsqltype="cf_sql_varchar" list="true" value="#d#">)
</cfquery>
dbetke
  • 28
  • 5
  • It would know the value of c because he's defined it on the page. – Matt Busche Mar 21 '14 at 11:35
  • Coldfusion knows the value of c, but the query does not when it is sent in using cfqueryparam. If you do so and dump the results onto the page, you can see that c is replaced by ?. Perhaps I misunderstood the question. It looked to me that the variable c representing the column name, which is then searching for values stored that are in list d. – dbetke Mar 21 '14 at 15:36
  • It's replaced by ? because it's added as a param below the query, that's not the issue. – Matt Busche Mar 21 '14 at 15:53
  • You may have misunderstand the question. Variable c is value 12345. The column name is d. However, you did post the query that the OP claimed to be "good". – Dan Bracuk Mar 21 '14 at 18:18
  • I perhaps did not help matters by using `c` and `d` in two different forms, however in example 1, C represents a table column and D represents the ColdFusion variable, and visa versa in example 2. – Banny Mar 24 '14 at 16:54