4 Replies Latest reply on Sep 22, 2008 7:12 AM by tomtomtom

    List functions: Comparing lists... in SQL statements?

    tomtomtom Level 1
      Hello

      I got a table with a column containing comma-separated values, p.e.
      row 1: 45,67,2,90,67
      row 2: 34,7,23,9,7
      row 3: 4
      row 4: 567,8,90

      now I would like to find the rows containing p.e. 4 and 90. the search input comes in a list 4,90
      my results should be row 1, row 3 and row 4.

      how do I write the WHERE clause in SQL?

      thank you so much for hints...
        • 1. Re: List functions: Comparing lists... in SQL statements?
          Dan Bracuk Level 5
          Normalize your database and you won't have problems like this. If you didn't understand the 1st 3 words, I have heard good things about the book, Database Design for Mere Mortals.
          • 2. Re: List functions: Comparing lists... in SQL statements?
            tomtomtom Level 1
            You are right, I have no idea what 'Normalize your database' means! But actually my project is at a state where I don't touch the database design any longer. So unfortunately your input doesn't help much....

            But thx anyway
            • 3. Re: List functions: Comparing lists... in SQL statements?
              Kronin555 Level 1
              As Dan alluded to, you need to fix your data model. However, you say you can't do this, but you ask: "how do I write the WHERE clause in SQL?"

              SQL isn't designed to help you continue to use a faulty data model. Thus, there isn't a nice way to write what you want, and it's going to be dog-slow.

              For each item in the list you're searching for (4,90 in your example), you need to check 4 options:
              Is this element at the beginning of my list? WHERE row LIKE '#element#,%'
              Is this element at the end of my list? WHERE row LIKE '%,#element#'
              Is this element in the middle of my list? WHERE row LIKE '%,#element#,%'
              Is this the only element in the list? WHERE row = '#element#'
              You can see how this gets crazy.

              <cfset listToSearchFor = "4,90">
              <cfquery ...>
              SELECT * FROM tablename WHERE
              <cfloop list="#listToSearchFor#" index="idx">
              row LIKE '#idx#,%' OR
              row LIKE '%,#idx#' OR
              row LIKE '%,#idx#,%' OR
              row = '#idx#' OR
              </cfloop>
              1 = 2
              </cfquery>

              All of these OR LIKE checks are gonna make the query slower and slower.

              In the future, never EVER store a comma-delimited list in a database field. You want to break it out into another table. How do you do that? I'm glad you asked... In your example, where Row 1 = "45,67,2,90,67", assume the primary key for that row is "12"...

              Main_Table
              ---------------
              id

              Whatever_Table (You'd name this table based on what these ids stand for in the row list)
              --------------------
              main_id foreign key references main_table(id)
              whatever_id

              So your Main_Table would have an entry with an ID of 12 and Whatever_Table would contain:
              main_id whatever_id
              12 45
              12 67
              12 2
              12 90
              12 67

              Now you can get all the whatever_id values for a given main_id by:
              select whatever_id from whatever_table where main_id = 12;

              And to answer your original question, you can do:
              SELECT Main_Table.* FROM Main_Table JOIN Whatever_Table ON (Main.id = Whatever_Table.main_id)
              WHERE whatever_id IN (4,90)

              Lots easier, huh?
              • 4. Re: List functions: Comparing lists... in SQL statements?
                tomtomtom Level 1
                Dear Dan, dear Kronin555

                I'm sorry about saying that I wasn't able to fix my data model. After sleeping a night over and dreaming about it, I changed the model as you guys suggested and it works fine.... I didn't understand right away what you meant!

                Be sure I will show up again with many stupid questions :-)

                So, many thanks for your inputs.