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:

Table1:

|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    |

Table2:

|id |item       |
|1  |Mobile     |
|2  |Creditcard |
|3  |Wallet     |
|4  |Car        |
|22 |House      |
|23 |Boat       |

Expected result

4|Jorn|Car
4|Jorn|House
4|Jorn|Boat

I tried

SELECT * 
FROM Table1 
INNER JOIN Table2 ON Table1.itemsid LIKE CONCAT ('%', Table2.id, '%') 
WHERE Table1.name = Jorn

Resulting in

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   

Resulting in

4|Jorn|Car

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.

Answers


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.


Need Your Help

Error to execute powershell: System.Diagnostics.Process.StartWithShellExecuteEx

c# powershell

I try execute powershell script with HelloWorld, but I see error.My script:

IAM allowing a user to access everything for ec2 on a region

amazon-web-services amazon-ec2 amazon-iam

I'm trying to allow one user to all actions on us-west-2, this is the policy I have.

How scale up/out mechanism works in OpenStack and Cloudfoundry integration?

cloud openstack cloudfoundry bosh

I just started investigating OpenStack & Cloudfoundry recently, and I just wonder how scale up/out mechanism works in OpenStack & Cloudfoundry integration.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.