Select rows from comma collected ids in a column

Question

My db is:

# item_rel
id | item_ids
--------------------------
1  | 1,2,4,6,8,10
2  | 3,5,7
3  | 9,11,12
.. | ............

# items
id | name
--------------------------
1  | Lorem
2  | Ipsum
3  | Sed
4  | Amed
.. | ............

The problem is select items with one query. I have tried many way but no luck. Only this one approachs the expected result:

SELECT
    items.*
FROM
    items
WHERE
    items.id IN (
        SELECT
            items_rel.ids
        FROM
            items_rel
        WHERE
            items_rel.ids LIKE "1" OR
            items_rel.ids LIKE "1,%" OR
            items_rel.ids LIKE "%,1" OR
            items_rel.ids LIKE "%,1,%"
    )

This query returns only one item.. How to get every item with one query?

Answer 1

This was a bit tricky, but was so not imposible, nor a sign of ignorance:

SET @ids = (SELECT ids FROM item_rel
    WHERE ids LIKE '1' OR ids LIKE '1,%' OR ids LIKE '%,1' OR ids LIKE '%,1,%') ;
SET @ids = (SELECT IF (@ids IS NOT NULL,@ids,'99999999999999999999999')) ;
SET @q = CONCAT('SELECT * FROM items WHERE ID IN (', @ids,')') ;
PREPARE stmt FROM @q ;
EXECUTE stmt ;

"99999999999999999999999" must be any value that never found in your db.

Answer 2

There are many, many reasons not to store ids in comma-delimited lists:

• Values should be stored using the appropriate type. Numbers should not be stored as strings.
• Foreign keys should be properly declared. You cannot declare foreign key relationships.
• SQL tables traditionally have one value per column, not a variable number.
• SQL has pretty poor string processing capabilities.
• Queries on such structures cannot readily make use of indexes.
• SQL has a really great data structure for storing lists. It is called a table.

Sometimes, we are stuck with other people's really, really, really, really, really, really bad decisions.

MySQL offset a work-around called find_in_set():

SELECT i.*
FROM items i JOIN
     items_rel ir
     ON find_in_set(i.id, ir.ids) > 0;

However, you should put your effort into fixing the data model, not getting the query to work. Wikipedia is one place to start.

Answer 3

First of all, as said above please redesign the tables. But if you cannot, the way to make this work is to add some more Commas in the query-- that is to add a leading and trailing comma to satisfy the Where...LIKE...

SELECT
    Rows_items.*
FROM
    Rows_items
WHERE
    Rows_items.id IN (
        SELECT
            Rows_item_rel.id
        FROM
            Rows_item_rel
        WHERE
            ',' + Rows_item_rel.item_ids + ','  LIKE '%,' + '1' + ',%' 

    )