How to Join Tables with Comma-Separated Values in SQL?

Join Two Tables with Comma-Separated Values

Problem:

We have two tables: Notes and Positions. The Notes table contains a column called 'forDepts' that stores comma-separated department IDs. We need to join these tables and associate the 'forDepts' values with the corresponding department names from the Positions table.

Desired Output:

nid	DepartmentName
1	Executive, Corp Admin, Art
2	Art, Marketing

Solution:

Since the database structure cannot be modified, we will use the following SQL query to achieve the desired output:

SELECT  a.nid,
        GROUP_CONCAT(b.name ORDER BY b.id) DepartmentName
FROM    Notes a
        INNER JOIN Positions b
            ON FIND_IN_SET(b.id, a.forDepts) > 0
GROUP   BY a.nid

Explanation:

• The GROUP_CONCAT function is used to concatenate the 'name' column values from the Positions table for each row in the Notes table.
• The ORDER BY clause ensures that the department names are ordered by their IDs.
• The FIND_IN_SET function checks if the department ID from the Positions table exists in the 'forDepts' column of the Notes table. If it does, the corresponding row is included in the join.
• The GROUP BY clause groups the results by the 'nid' column in the Notes table, effectively combining all the department names associated with each note.

By executing this query, we can obtain the desired output, where the 'forDepts' column values from the Notes table are replaced with the corresponding comma-separated department names from the Positions table. This will facilitate the export of the data into an Excel file using the provided PHP code.

The above is the detailed content of How to Join Tables with Comma-Separated Values in SQL?. For more information, please follow other related articles on the PHP Chinese website!