Storing IDs as comma separated values

Question

How can I select from a database all of the rows with an ID stored in a varchar comma separated. for example, I have a table with this:

, 7, 9, 11

How can I SELECT the rows with those IDs?

Answer 1

Normalize your database. You should be using a lookup table most likely.

Answer 2

You have 2 options:

1. Use a function to split the string into a temp table and then join the table your selecting from to that temp table.
2. Use dynamic SQL to query the table where id in (@variable) --- bad choice if you choose this way.

Answer 3

select * from table_name where id in (7, 9, 11)

If you do typically have that comma at the start, you will need to remove it first.

Answer 4

Use match(column) against('7,9,11')

this willl show all varchar column of your id's where 7,9,11 is there. But you have to be shure that ur column have fulltext index.

Answer 5

Just yesterday I was fixing a bug in an old application here and saw where they handled it like this:

AND (T.ServiceIDs = '#SegmentID#' OR T.ServiceIDs LIKE '#SegmentID#,%'
                OR T.ServiceIDs LIKE '%,#SegmentID#,%' OR T.ServiceIDs LIKE '%,#SegmentID#')

I am assuming you are saying something like the value of ServiceIDs from the database might contain 7,9,11 and that the variable SegmentID is one or more values. It was inside a CFIF statement checking to see that SegmentID in fact had a value(which was always the case due to prior logic that would default it.

I personally though would do as others have suggested and I'd create what I always refer to as a bridging table that allows you to have 0 to many PKs from one table related to the PK of another.

I had to tackle this problem years ago where I could not change the table structure and I created a custom table type and a set of functions so I could treat the values via SQL as if they were coming from a table. That custom table type solution though was specific to Oracle and I'd not know how to do that in MySQL without some research on my part.

Answer 6

There is a reason querying lists is so difficult: databases are not designed to work with delimited lists. They are optimized to work best with rows (or sets) of data. Creating the proper table structure will result in much better query performance and simpler sql queries. (So while it is technically possible, you should seriously consider normalizing your database as Todd and others suggested.)

Many-to-many relationships are best represented by three (3) tables. Say you are selling "widgets" in a variety of "sizes". Create two tables representing the main entities:

Widget (unique widgets)

WidgetID | WidgetTitle
1        | Widget 1
2        | Widget 2
....

Size (unique sizes)

SizeID  | SizeTitle
 7      | X-Small
 8      | Small
 9      | Medium
10      | Large
11      | X-Large

Then create a junction table, to store the relationships between those two entities, ie Which widgets are available in which sizes

WidgetSize (available sizes for each widget)

WidgetID | SizeID
1        | 7         <== Widget 1  "X-Small"
1        | 8         <== Widget 1 + "Small"
2        | 7         <== Widget 2 + "X-Small"
2        | 9         ....
2        | 10
2        | 11
....

With that structure, you can easily return all widgets having any (or all) of a list of sizes. Not tested, but something similar to the sql below should work.

• Find widgets available in any of the sizes: <cfset listOfSizes = "7,9,11">

   SELECT w.WidgetID, w.WidgetTitle
   FROM   Widget w 
   WHERE  EXISTS 
         (   SELECT 1 
             FROM   WidgetSize ws 
             WHERE  ws.WidgetID = w.WidgetID
             AND    ws.SizeID IN ( 
                     <cfqueryparam value="#listOfSizeIds#" 
                            cfsqltype="cf_sql_integer" list="true" > 
                    )
         )
  

• Find widgets available in all three sizes: <cfset listOfSizes = "7,9,11">

    SELECT   w.WidgetID, w.WidgetTitle, COUNT(*) AS MatchCount
    FROM     Widget w INNER JOIN WidgetSize ws ON ws.WidgetID = w.WidgetID
    WHERE    ws.SizeID IN ( 
                     <cfqueryparam value="#listOfSizeIds#" 
                            cfsqltype="cf_sql_integer" list="true" > 
             )
    GROUP BY w.WidgetID, w.WidgetTitle
    HAVING   MatchCount = 3