Can I Join Tables in MySQL Based on Values Separated by Semicolons?

Can I Resolve This with Pure MySQL? (Joining on ';' Separated Values in a Column)

Question:

I have data in multiple tables that I need to join based on ';' (semicolon) separated values in a column. I cannot use PHP or any other language to manipulate the results. Is there a way to achieve this using only MySQL?

Answer:

Yes, it is possible to join on ';' separated values in a column using pure MySQL. Below is a detailed explanation of the solution:

Converting the Delimited String into Rows

The key is to convert the ';' delimited string into a series of rows. This can be achieved using the following steps:

1. Create a table named integerseries containing the numbers from 1 to the maximum number of items in the delimited string.
2. Use the COUNT_IN_SET and VALUE_IN_SET functions to count the number of items in the delimited string and extract each item based on its position.
3. Join the user_resource table with the integerseries table using a cross join. This will generate rows for each item in the delimited string.

Query to Normalize the User_resource Table

<code class="sql">SELECT user_resource.user, 
       user_resource.resources,
       COUNT_IN_SET(user_resource.resources, ';') AS resources_count, 
       isequence.id AS resources_index,
       VALUE_IN_SET(user_resource.resources, ';', isequence.id) AS resources_value
FROM 
     user_resource 
     JOIN  integerseries AS isequence 
       ON  isequence.id <= COUNT_IN_SET(user_resource.resources, ';')
ORDER BY
       user_resource.user, isequence.id</code>

Joining the Normalized Table with the Resource Table

Once the user_resource table is normalized, it can be joined with the resource table to obtain the desired result.

<code class="sql">SELECT user_resource.user, 
       resource.data

FROM user_resource 
     JOIN integerseries AS isequence 
       ON isequence.id <= COUNT_IN_SET(user_resource.resources, ';')

     JOIN resource 
       ON resource.id = VALUE_IN_SET(user_resource.resources, ';', isequence.id)      
ORDER BY
       user_resource.user,  resource.data</code>

Sample Input and Output

user_resource Table:

user	resources
user1	1;2;4
user2	2
user3	3;4

resource Table:

id	data
1	data1
2	data2
3	data3
4	data4
5	data5

Normalizeduser_resource Table:

user	resources	resources_count	resources_index	resources_value
user1	1;2;4	3	1	1
user1	1;2;4	3	2	2
user1	1;2;4	3	3	4
user2	2	1	1	2
user3	3;4	2	1	3
user3	3;4	2	2	4

Output (Joined Result):

user	data
user1	data1
user1	data2
user1	data4
user2	data2

The above is the detailed content of Can I Join Tables in MySQL Based on Values Separated by Semicolons?. For more information, please follow other related articles on the PHP Chinese website!