-3

I have a table in which there is a column that stores id(s) of another table, there could be a single id or multiple ids separated by a comma (,).

I want to search multiples values against the column: "s" which again can have multiple values. So searching: "5, 3, 1" should match with the column "id" 2, 4, 5, 6 in the below table

Similarly, search "4, 5" should match with the column "id" 6, 7, 8.

So how can I get these rows distinctively?

| id | s    |
+----+------+
|  1 | 2    |
|  2 | 1    |
|  3 | 2    |
|  4 | 2,3  |
|  5 | 1,3  |
|  6 | 1,2,5|
|  7 | 4,5  |
|  8 | 3,4  |
+----+------+

Please help!

ADyson
  • 57,178
  • 14
  • 51
  • 63
Takshak Rajput
  • 119
  • 1
  • 14
  • 4
    The reason you have this problem is because this isn't how relational databases are supposed to be used. If you structure your tables and relationships properly in a normalised way, such a query would be simple, and achievable using basic SQL. Please read [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). If you ever find yourself storing multiple values in a single column, remember that it's a sign you haven't designed your database correctly. – ADyson Mar 23 '22 at 11:33
  • 3
    Fix the data model. When you have a column of comma-separated values, what you *want* is a separate table with a foreign key relationship. Normalizing the data will make it much easier to query. – David Mar 23 '22 at 11:33
  • 2
    As the previous commenters said: Fix your data model. But in case you are hell-bent on continuing with your already flawed approach: [MySQL query finding values in a comma separated string](https://stackoverflow.com/questions/5033047/mysql-query-finding-values-in-a-comma-separated-string) – CBroe Mar 23 '22 at 11:35
  • Thank you all for your feedback, I do understand the model isn't right, I don't any choice and things are messy in the current project, and making the changes would have a chain reaction. The answer by @akina works great which is what I need as of now. – Takshak Rajput Mar 23 '22 at 13:23
  • It'd be a good chain reaction, though. Definitely needs adding to your backlog as technical debt. – ADyson Mar 23 '22 at 13:24

1 Answers1

1
SELECT *
FROM test 
WHERE JSON_OVERLAPS(CONCAT('[', test.s, ']'), @criteria_as_json)

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=8f5043a3734d9b7ded81597a26b09ae7

Akina
  • 39,301
  • 5
  • 14
  • 25