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

Joining Tables with Comma-Separated Values: A Complete Guide

Database tables often contain comma-separated values in certain columns. This poses a challenge when querying the data because each value needs to be associated with its corresponding row. One common scenario is joining two tables where one column contains comma-separated values referencing the other table.

Consider the following example:

Notes Table

nid	forDepts
1	1,2,4
2	4,5

Positions Table

id	name
1	Executive
2	Corp Admin
3	Sales
4	Art
5	Marketing

The goal is to associate the 'forDepts' column in the Notes table with the corresponding department names from the Positions table. The desired output should be:

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

Without changing the database structure, this can be achieved using a combination of SQL functions.

Using FIND_IN_SET and GROUP_CONCAT

A combination of the FIND_IN_SET and GROUP_CONCAT functions can be used to extract and aggregate the department names for each 'forDepts' value:

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

How it Works

• The FIND_IN_SET function checks if a specified value exists within a comma-separated string. In this case, it is used to determine if the 'forDepts' value in the Notes table contains the id of a department in the Positions table.
• The INNER JOIN clause matches rows from the Notes and Positions tables where the FIND_IN_SET condition is true.
• The GROUP_CONCAT function aggregates the matching department names for each 'nid' in the Notes table. The ORDER BY clause ensures that the names are sorted in ascending order based on their id.

Result

The output of the query would be the desired table with the 'nid' column joined with the corresponding comma-separated 'DepartmentName' column.

Conclusion

By leveraging the FIND_IN_SET and GROUP_CONCAT functions, we can effectively join two tables with comma-separated values, enabling us to extract and aggregate data across tables, even when the database structure is not normalized. This technique proves particularly useful when exporting data to other applications, such as Excel.

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