Search in a comma delimited string from the same table

Question

How to get the id and name of favourite(s) of a user.

The table 'users'

id | name | favourites
----------------------
1  | Ian  | 2,3
2  | Dave | 1
3  | Mike | 1,2

(Note: favourites is a string.)

I tried the following, with zero rows as a result:

SELECT id, name FROM users WHERE id IN (SELECT favourites FROM users WHERE id=1)

The result of the query for the favourites of Ian (id 1) should be

id | name
---------
2  | Dave
3  | Mike

Thanks for having a look!

you should not buse comma separated value for store collection of id . you should normalize correctly your tables — ScaisEdge, Jul 15 '19 at 17:04
Related: [is-storing-a-delimited-list-in-a-database-column-really-that-bad](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) — Paul Spiegel, Jul 15 '19 at 17:07
when we do `SELECT favorites` what is returned is a string, the outer query is essentially doing a `WHERE id = '2,3'` looking for a string match. The comma separator is just a character in the string. The comma inside that string is not interpreted as part of the SQL text. We can do `WHERE id IN (2,3)` but in this case, the comma is a token in the SQL text, so that this this gets parsed equivalent to `WHERE id = 2 OR id = 3`. If we do `WHERE id IN ('2,3')` thats the same as `WHERE id = '2,3'`. (This should help explain the observed behavior, why the query returning zero rows.) — spencer7593, Jul 15 '19 at 17:21
+10 this question was well asked, provided example data, expected output, and the exact SQL text being used, which is way more than most MySQL questions that get asked. OP is asking about behavior that is observed. It's really more like the question "why is the comma in my string not parsed as a SQL token". (*A:* SQL doesn't do that because SQL would be very broken if it did.) Note that it is actually possible to extract the individual elements from the comma separated list (up to a finite limit) and write a query that returns the expected result given the tables as shown, but the SQL is ugly. — spencer7593, Jul 15 '19 at 17:34
`SELECT f.id, f.name FROM users f JOIN users u ON FIND_IN_SET(f.id,u.favourites) WHERE u.id=1 ORDER BY f.id` ref: https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_find-in-set — spencer7593, Jul 15 '19 at 17:38
@spencer7593 Then you should write an answer. IMHO your first comment also shows more quality than most MySQL answers. I'm though quite sure that it's a duplicate question. It's just difficult to find a good reference because there are too many of them. — Paul Spiegel, Jul 15 '19 at 17:42
@PaulSpiegel: question is closed, can't be answered. Note that I do agree that storing multivalued attributes as a comma separated list is an antipattern, Chapter 2 "Jaywalking" in Bill Karwin's book https://www.amazon.com/SQL-Antipatterns-Programming-Pragmatic-Programmers/dp/1934356557 gives sufficient treatment to the topic. Yes, I'm sure this same question has been asked numerous times, but StackOverflow is so cluttered with mis-titled questions it takes an inordinate amount of effort to find it. I'm not opposed to close as duplicate. I've just chosen not to downvote the question. — spencer7593, Jul 15 '19 at 17:45
@spencer7593 IMHO nothing is wrong with good formulated duplicate questions. They add more search patterns. So I agree on not downvoting. Though in this case the title isn't helpful and adds noise. — Paul Spiegel, Jul 15 '19 at 18:01

ScaisEdge · Accepted Answer · 2019-07-15T19:03:20.167

You should not use comma separated value for store collection of id
but use a proper normalizad table

table users 
id | name 
-----------
1  | Ian  
2  | Dave 
3  | Mike 

table favorites 
id , id_user, id_favorite 
1, 1, 2
2, 1, 3 
3, 2, 1
4, 3, 1
5, 3, 2

select u.id, u.name 
from users u
inner join  favorites f on f.id_favorite = u.id
where f.id_user = 1

edit by spencer7593

Storing comma separated values in a column is an antipattern. It violates first normal form.

To answer more directly the question that was asked: it is possible to write a MySQL query that returns the specified result.

MySQL provides a FIND_IN_SET function that returns the position of an element found in a comma separated list. Zero is returned when the element is not found.

We can do something like this:

SELECT f.id
     , f.name
  FROM users f
  JOIN users u 
    ON FIND_IN_SET(f.id,u.favourites) 
 WHERE u.id = 1
 ORDER
    BY f.id

Reference: https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_find-in-set

Besides making clear why my solution did not work (and even giving a working solution for that), I learned about normalization and I will use that. Thanks! — Dick, Jul 16 '19 at 10:15
+10 I emphatically concur with comma separated list is antipattern violation of first normal form every attribute is dependent on the key the whole key and nothing but the key so help me Codd. — spencer7593, Jul 16 '19 at 20:17

Search in a comma delimited string from the same table

1 Answers1