SQL like concat doesn't select specific value
I have two tables where in one field I need to search for a value in another row, the row is a concatenated string. Something like this:
|id|Name |Itemsid | |1 |John |1 | |2 |Hans |4, 22, 23| |3 |Chris|2, 4, | |4 |Jorn |4, 22, 23| |5 |Claus|1, 4, 23 | |6 |Marco|22, 4 |
|id |item | |1 |Mobile | |2 |Creditcard | |3 |Wallet | |4 |Car | |22 |House | |23 |Boat |
4|Jorn|Car 4|Jorn|House 4|Jorn|Boat
SELECT * FROM Table1 INNER JOIN Table2 ON Table1.itemsid LIKE CONCAT ('%', Table2.id, '%') WHERE Table1.name = Jorn
4|Jorn|Car 4|Jorn|Creditcard 4|Jorn|Wallet 4|Jorn|House 4|Jorn|Boat
Therefore I tried a regexp so the inner join would be:
SELECT * FROM Table1 INNER JOIN Table2 ON Table1.itemsid REGEXP CONCAT ('(,|\s|^)', Table2.id, '(,|\s|$)') WHERE Table1.name = Jorn
So the problem using like concat, is that it doesn't select the specific value but in also select 2 if the id is 22.
Problem with regexp is that it only select the first id, and don't go through the list.
So I am looking for a query that will give me the expected result instead.
You have a delimiter problem. You can solve this with:
SELECT * FROM Table1 INNER JOIN Table2 ON concat(', ', Table1.itemsid, ', ') LIKE CONCAT ('%, ', Table2.id, ', %') WHERE Table1.name = 'Jorn';
However, you should really use a junction table instead. Storing lists of integers in strings is a bad idea. SQL has a great construct for storing lists. It is called a table, not a string.