How to Join Data from Multiple Tables with Delimited Values in MySQL Without Linking Tables?

Joining Data from Multiple Tables Using Delimited Values in MySQL

Question:

How can I efficiently retrieve data from multiple tables that have semicolon-separated values in one of the columns, without using external languages or introducing a linking table?

Answer:

To address this issue, we can utilize a technique that converts the semicolon-separated values into individual rows. This allows us to join the tables and obtain the desired result.

Solution:

The provided solution employs two custom MySQL functions:

• COUNT_IN_SET(haystack, delim): Returns the count of character-delimited items in a given string.
• VALUE_IN_SET(haystack, delim, which): Treats the delimited list as an array and retrieves the value at a specified index (which).

These functions are combined with a join between the user and resource tables, using the integerseries table to iterate through the semicolon-separated values:

<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, ';') /* normalize */

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

This query results in a table where each user's resources are represented as individual rows, enabling us to easily join with the resource table to obtain the corresponding data.

Additional Information:

• The integerseries table contains a sequence of values used to normalize the delimited lists.
• The COUNT_IN_SET and VALUE_IN_SET functions can be reused in other scenarios where delimited lists need to be processed.
• For SQLite databases, a similar solution is available: https://stackoverflow.com/a/24750820/302036

The above is the detailed content of How to Join Data from Multiple Tables with Delimited Values in MySQL Without Linking Tables?. For more information, please follow other related articles on the PHP Chinese website!