Join two tables with comma separated values

Question

I have 2 tables as below

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       ║
╚══════════╩═════════════════╝

I am looking to query my Notes table and associate the 'forDepts' column with values from the Positions table.

The output should be:

    ╠══════════╬════════════════════════════╣
    ║ 1        ║ Executive, Corp Admin, Art ║
    ║ 2        ║ Art, Marketing             ║
    ╚══════════╩════════════════════════════╝

I know that the database should be normalized but I cannot change the database structure for this project.

This is going to be used to export a excel file with the code below.

<?PHP

    $dbh1 = mysql_connect($hostname, $username, $password); 
    mysql_select_db('exAdmin', $dbh1);


    function cleanData(&$str)
  {
    $str = preg_replace("/\t/", "\\t", $str);
    $str = preg_replace("/\r?\n/", "\\n", $str);
    if(strstr($str, '"')) $str = '"' . str_replace('"', '""', $str) . '"';
  }
    
    $filename = "eXteres_summary_" . date('m/d/y') . ".xls";
    
    header("Content-Disposition: attachment; filename=\"$filename\"");
    header("Content-Type: application/vnd.ms-excel");
    //header("Content-Type: text/plain");
    
    $flag = false;


    $result = mysql_query(
    "SELECT p.name, c.company, n.nid, n.createdOn, CONCAT_WS(' ',c2.fname,c2.lname), n.description 
     FROM notes n 
     LEFT JOIN Positions p ON p.id = n.forDepts
     LEFT JOIN companies c ON c.userid = n.clientId
     LEFT JOIN companies c2 ON c2.userid = n.createdBy"
     , $dbh1);
 

    
    while(false !== ($row = mysql_fetch_assoc($result))) {
        if(!$flag) {
            $colnames = array(
                'Created For' => "Created For",
                'Company' => "Company",
                'Case ID' => "Case ID",
                'Created On' => "Created On",
                'Created By' => "Created By",
                'Description' => "Description"
            );
            // display field/column names as first row
            echo implode("\t", array_keys($colnames)) . "\r\n";
            $flag = true;
    }
    

        $row['createdOn'] = date('m-d-Y | g:i a', strtotime($row['createdOn']));
    
    array_walk($row, 'cleanData');
    echo implode("\t", array_values($row)) . "\r\n";
  }
  exit;
  
  ?>

This code outputs only the first value of 'forDepts'

Exa: Executive (instead of Executive, Corp Admin, Art)

Can this be accomplished by CONCAT or FIND_IN_SET?

If you can't change the bad database layout, you should probably fix it OUTSIDE of the database, i.e. you query the data you get and exchange the ids with their text values in PHP before pushing them to the Excel file. Sadly, the nasty database layout forces you to create nasty layout-fixing code. — Sven, Sep 30 '13 at 18:52

score 133 · Accepted Answer · answered Sep 30 '13 at 18:53

133

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

SQLFiddle Demo

answered Sep 30 '13 at 18:53

John Woo

258,903
69
498
492

If a row in the `Notes` table have a blank value for column `forDepts` then result missing that row. – Kishor Patidar Aug 20 '19 at 11:10
@JohnWoo What is the purpose of `> 0` at the end of the join ? Is that related to performance? – Silambarasan R Nov 29 '20 at 11:12

Tirthraj Singh Bartwal · Answer 2 · 2015-04-30T11:07:48.893

Table 1
╔══════════╦═════════════════╗
║ nid      ║    forDepts     ║
╠══════════╬═════════════════╣
║ 1        ║ 1,2,4           ║
║ 2        ║ 4,5             ║
╚══════════╩═════════════════╝
Table 2
╔══════════╦═════════════════╗
║ id       ║    name         ║
╠══════════╬═════════════════╣
║ 1        ║ Executive       ║
║ 2        ║ Corp Admin      ║
║ 3        ║ Sales           ║
║ 4        ║ Art             ║
║ 5        ║ Marketing       ║
╚══════════╩═════════════════╝

SELECT * FROM table1 as t1 LEFT JOIN table2 as t2 ON find_in_set(t2.id, 
t1.forDepts)

Output 

 ╠══════════╬════════════════════════════╣
 ║ 1        ║ Executive, Corp Admin, Art ║
 ║ 2        ║ Art, Marketing             ║
 ╚══════════╩════════════════════════════╝

The issue with this approach is that the query takes ages to complete when there are large amount of data. — KayKay, Jan 05 '20 at 16:48

Join two tables with comma separated values

2 Answers2

Linked

Related