Home > Database > Mysql Tutorial > body text

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

Barbara Streisand
Release: 2024-11-01 00:03:28
Original
1019 people have browsed it

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>
Copy after login

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>
Copy after login

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!

source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Articles by Author
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template