10 Replies Latest reply on Apr 23, 2007 4:53 PM by The ScareCrow

    SQL IN statement

    fshin
      Hi,

      I'm trying to run a query that looks something like this

      <cfquery name="test" datasource = "ds1">
      SELECT *
      FROM tblTest
      WHERE '#variables.test#' IN (txCommaDelimitedList)
      </cfquery>

      where variables.test is an integer and txCommaDelimitedList is a database field like '100','101','102'. This doesnt work though. Any ideas?
        • 1. Re: SQL IN statement
          Dan Bracuk Level 5
          Normalize your database and you won't have problems like this.
          • 2. SQL IN statement
            dallaswebb
            You'd need to loop over variables.test and create dynamic statement.

            <cfset tempSQLStatement ="WHERE #listFirst(variables.test)# IN (txCommaDelimitedList)" />

            <cfloop list="#listRest(variables.test)#" index="i">
            <!--- you could also use OR --->
            <cfset tempSQLStatement = "AND #i# IN (txCommaDelimitedList)" />

            </cfloop>

            <cfquery name="test" datasource = "ds1">
            SELECT *
            FROM tblTest
            #tempSQLStatement#
            </cfquery>
            • 3. Re: SQL IN statement
              joeDangelo Level 1
              Whats the error you get?
              • 4. Re: SQL IN statement
                fshin Level 1
                Actually, my example was bad. It should have read:

                SELECT *
                FROM view_test
                WHERE '#variables.test#' IN (txCommaDelimitedList)

                It's a view where txCommaDelimitedList is a field which contains a comma delimited list of integers based on the results of a query of an xref table.

                No errors. Just no results.
                • 5. Re: SQL IN statement
                  joeDangelo Level 1
                  Why is variables.test in quotes?
                  • 6. Re: SQL IN statement
                    fshin Level 1
                    bc txCommaDelimitedList is a varchar field since it has the commas in sql
                    • 7. SQL IN statement
                      paross1 Level 2
                      so which one is your field, and which one is your set of values? You can NOT do this:

                      SELECT something
                      FROM table
                      WHERE 'value' IN (COLUMN_name1, COLUMN_Name2, etc.)

                      This is invalid SQL!

                      Should be more like

                      WHERE COLUMN_Name IN('value','value','etc.')

                      Phil
                      • 8. Re: SQL IN statement
                        insuractive Level 3
                        I can see a few obvious problems with the SQL:

                        1) You should not use quotes surrounding #variables.test#. The resulting SQL should be:

                        WHERE myCol IN (myList)

                        and not
                        WHERE 'myCol' IN (myList)

                        2) You don't have txCommaDelimitedList surrounded by pound signs, so CF is treating it as the literal text: "txCommaDelimitedList". Look into using <cfqueryparam> you can use the list="Yes" parameter to handle comma delimited lists (even integers).

                        WHERE #Variables["test"]# IN (<cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#txCommaDelimitedList#" list="Yes">)

                        3) Using a CF variable like that in your query statement is incredibly insecure. You're pretty much setting yourself up for a SQL injection attack. You might look into using a switch/case statement, or at least performing an initial query to make sure that Variables.test is a valid column name and not something like:

                        1=1;
                        DROP TABLE
                        SELECT * from SomeTable WHERE 1
                        • 9. Re: SQL IN statement
                          Kronin555 Level 1
                          I'll echo what Dan said:

                          > Normalize your database and you won't have problems like this.

                          What you're trying to do is not possible:

                          WHERE 'cfvariable' IN (databasecolumn)
                          where the databasecolumn contains values like this:
                          "'101','200','204','204'"

                          You're trying to do a reverse IN. SQL IN statements are meant to check a database column against a comma-delimited list of values, not the other way around (a value against a column in your database that contains a comma-delimited list of values).

                          You need to fix your data model.
                          • 10. Re: SQL IN statement
                            The ScareCrow Level 1
                            My 2c,

                            Because fshin has said
                            quote:

                            It's a view where txCommaDelimitedList is a field which contains a comma delimited list of integers based on the results of a query of an xref table.


                            I would then say the db may be normalized.

                            Questions:
                            How does the cf page access this "view" ?
                            Can you change the "view" or pass in a parameter ?

                            The query you are trying to perform can't be done.

                            Maybe easier to use list functions to see if the "test" value is in the "view" list, then use the result in a query.

                            Ken