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 <p><strong>Joining the Normalized Table with the Resource Table</strong></p>
<p>Once the user_resource table is normalized, it can be joined with the resource table to obtain the desired result.</p>
<pre class="brush:php;toolbar:false"><code class="sql">SELECT user_resource.user, 
       resource.data

FROM user_resource 
     JOIN integerseries AS isequence 
       ON isequence.id <p><strong>Sample Input and Output</strong></p>
<p><strong>user_resource Table:</strong></p>
<table>
<thead><tr>
<th>user</th>
<th>resources</th>
</tr></thead>
<tbody>
<tr>
<td>user1</td>
<td>1;2;4</td>
</tr>
<tr>
<td>user2</td>
<td>2</td>
</tr>
<tr>
<td>user3</td>
<td>3;4</td>
</tr>
</tbody>
</table>
<p><strong>resource Table:</strong></p>
<table>
<thead><tr>
<th>id</th>
<th>data</th>
</tr></thead>
<tbody>
<tr>
<td>1</td>
<td>data1</td>
</tr>
<tr>
<td>2</td>
<td>data2</td>
</tr>
<tr>
<td>3</td>
<td>data3</td>
</tr>
<tr>
<td>4</td>
<td>data4</td>
</tr>
<tr>
<td>5</td>
<td>data5</td>
</tr>
</tbody>
</table>
<p><strong>Normalizeduser_resource Table:</strong></p>
<table>
<thead><tr>
<th>user</th>
<th>resources</th>
<th>resources_count</th>
<th>resources_index</th>
<th>resources_value</th>
</tr></thead>
<tbody>
<tr>
<td>user1</td>
<td>1;2;4</td>
<td>3</td>
<td>1</td>
<td>1</td>
</tr>
<tr>
<td>user1</td>
<td>1;2;4</td>
<td>3</td>
<td>2</td>
<td>2</td>
</tr>
<tr>
<td>user1</td>
<td>1;2;4</td>
<td>3</td>
<td>3</td>
<td>4</td>
</tr>
<tr>
<td>user2</td>
<td>2</td>
<td>1</td>
<td>1</td>
<td>2</td>
</tr>
<tr>
<td>user3</td>
<td>3;4</td>
<td>2</td>
<td>1</td>
<td>3</td>
</tr>
<tr>
<td>user3</td>
<td>3;4</td>
<td>2</td>
<td>2</td>
<td>4</td>
</tr>
</tbody>
</table>
<p><strong>Output (Joined Result):</strong></p>
<table>
<thead><tr>
<th>user</th>
<th>data</th>
</tr></thead>
<tbody>
<tr>
<td>user1</td>
<td>data1</td>
</tr>
<tr>
<td>user1</td>
<td>data2</td>
</tr>
<tr>
<td>user1</td>
<td>data4</td>
</tr>
<tr>
<td>user2</td>
<td>data2</td>
</tr>
</tbody>
</table></code>

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!