Join two tables with comma separated values

Join two tables with comma separated values

  1. Blog
  2. 15 November 2026
  3. 5 min read

Stefan Bogdanescu

Founder & Senior Architect

Join Two Tables With Comma-Separated Values

You're facing a classic data normalization issue: one table (notes) stores multiple department IDs in a single column as a comma-separated string, while another table (positions) holds the readable names for those IDs. Since you cannot alter the schema to normalize this data, you need a query strategy that joins on these delimited values without collapsing rows incorrectly or losing data.

The core problem is that standard SQL JOIN clauses expect unique matching keys. When forDepts contains multiple IDs (e.g., 1,2,4) and you join directly against Positions.id, a single row in notes will match multiple rows in positions. This typically results in a Cartesian product for that specific row, potentially duplicating other columns like company names or timestamps if they aren't handled carefully.

To fix this while keeping your output as comma-separated values in a single column, you need to split the delimited list into individual rows, perform the join, and then aggregate the results back together. This is often called an "explosion" and "aggregation" pattern.

Why Standard Joins Fail Here

Your current query attempts a direct join:

SELECT p.name 
FROM notes n 
LEFT JOIN Positions p ON p.id = n.forDepts

When n.forDepts is '1,2,4', the condition p.id = n.forDepts will never match because p.id is an integer (e.g., 1) and n.forDepts is a string ('1,2,4'). Even if you cast or strip the commas, standard SQL engines do not support matching multiple values in a single WHERE or ON clause without specific functions.

If you tried to use FIND_IN_SET, it would only return one row per match, but your current PHP code expects a single aggregated string per note record. The result set you need is essentially the sum of the matched positions for each note ID, formatted as a CSV.

The Solution: Split, Join, Aggregate

To achieve the desired output (Executive, Corp Admin, Art), you must first break the comma-separated list into individual numbers, join them with the positions table, and finally group the results by the original note ID to concatenate the names back together.

MySQL Implementation

If you are using MySQL (which your PHP code indicates via mysql_connect and mysql_query, though those extensions are deprecated), you can use FIND_IN_SET combined with a self-join or a generated series table to explode the values. However, the most robust approach for older MySQL versions involves creating a helper set of numbers if the forDepts column is stored as strings without spaces, or using REPLACE and SUBSTRING_INDEX logic depending on your specific delimiter handling.

A more modern and cleaner approach, assuming you can upgrade to MySQL 8.0+ or use a prepared statement environment, utilizes JSON_TABLE or recursive CTEs, but for legacy support (which your PHP code suggests), here is a pattern using FIND_IN_SET:

SELECT n.nid,
       GROUP_CONCAT(p.name ORDER BY p.id SEPARATOR ', ') AS positions
FROM notes n
CROSS JOIN (
    SELECT 1 as num UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
) nums
-- Note: This simple cross join won't work directly on the comma string.

Actually, for legacy MySQL without JSON support, the most reliable method to handle '1,2,4' specifically involves splitting the string. However, a simpler trick often used when the IDs are small integers is to use FIND_IN_SET inside a subquery or rely on the specific behavior of CONCAT_WS.

Since your source material asks if CONCAT or FIND_IN_SET can solve it: No single function does this in one pass. You must explode the list. Here is a working pattern for MySQL 5.x using a numbers table (or a generated set) to split the CSV:

SELECT n.nid,
       GROUP_CONCAT(p.name ORDER BY CAST(SUBSTRING_INDEX(n.forDepts, ',', n.num) AS UNSIGNED)) AS positions
FROM notes n
CROSS JOIN (
    SELECT 1 as num UNION ALL SELECT 2 UNION ALL SELECT 3 
    -- Add more numbers up to your max expected items per note
) nums
LEFT JOIN Positions p ON p.id = CAST(SUBSTRING_INDEX(n.forDepts, ',', n.num) AS UNSIGNED)
WHERE FIND_IN_SET(CAST(SUBSTRING_INDEX(n.forDepts, ',', n.num) AS UNSIGNED), n.forDepts) > 0
GROUP BY n.nid;

Note: The above query logic is simplified for explanation. In production MySQL 5.x without a numbers table, you might need to hardcode the cross join or use REPLACE if delimiters vary.

Integrating with Your PHP Export Script

Your current PHP script uses deprecated mysql_* functions and outputs tab-delimited data manually. To support the aggregated results, your query must return exactly one row per nid.

Once you have the correct SQL query that returns:

nid | positions
1   | Executive, Corp Admin, Art
2   | Art, Marketing

You can pass this directly to your PHP loop. However, be aware of two critical issues in your existing code:

  1. Deprecated Functions: The mysql_* extension has been removed in PHP 7.0+. You should migrate to mysqli or PDO.
  2. Data Cleaning: Your cleanData function handles tabs and newlines. Since you are exporting to Excel (.xls), ensure the output format matches what Excel expects for CSV-like data within a cell. If the position names contain commas, standard tab-delimited output might break if Excel interprets the first comma as a column separator unless you wrap the positions column in quotes.

Updated PHP Logic Example

// Use mysqli instead of mysql_*
$mysqli = new mysqli($hostname, $username, $password, 'exAdmin');

$query = "SELECT n.nid, GROUP_CONCAT(p.name SEPARATOR ', ') as positions 
          FROM notes n
          INNER JOIN Positions p ON FIND_IN_SET(p.id, n.forDepts)
          GROUP BY n.nid"; // Simplified for illustration; ensure your SQL handles the CSV split correctly

$result = $mysqli->query($query);

while ($row = $result->fetch_assoc()) {
    // Ensure the positions column is quoted if it contains commas to prevent Excel splitting columns
    echo "\t" . '"' . str_replace('"', '""', $row['positions']) . '"\r\n';
}

Trade-offs and Limitations

This approach relies on the assumption that the forDepts column is a well-formed CSV (no spaces around commas, consistent ordering). If your data quality varies, parsing logic becomes brittle. Furthermore, if a note has no matching positions, GROUP_CONCAT will return NULL or an empty string depending on your SQL mode.

While normalizing the database to have a many-to-many relationship table would be the ideal long-term solution, this query pattern allows you to maintain data integrity during export without schema changes. It effectively simulates a join over a denormalized structure by mathematically reconstructing the relationships before aggregation.

Our network