Home > Database > Mysql Tutorial > Can I Join Tables with Semi-colon Separated Values in a Column Using Pure MySQL?

Can I Join Tables with Semi-colon Separated Values in a Column Using Pure MySQL?

Barbara Streisand
Release: 2024-11-01 10:27:02
Original
899 people have browsed it

Can I Join Tables with Semi-colon Separated Values in a Column Using Pure MySQL?

Can I resolve this with pure mysql? (joining on ';' separated values in a column)

Question:

Given two tables, a user table t1 with a resources column containing ';' separated resource IDs, and a resource table t2 with an ID and data, you want to join t1 and t2 to get a table with user and resource data.

Problem:

You can't use external languages to manipulate the results and the user_resources table is denormalized.

Solution:

Create a table called integerseries with numbers from 1 to a certain limit. Use COUNT_IN_SET and VALUE_IN_SET functions, which treat the resources column as an array, to normalize the table. Then join with integerseries to extract individual resource IDs.

<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

The above is the detailed content of Can I Join Tables with Semi-colon Separated Values in a Column Using Pure MySQL?. 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