MySQL 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?

Please help! Thanks in advance!

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 109 · Accepted Answer · answered Sep 30 '13 at 18:53

109

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

238,432
61
456
464

@JohnWoo can Please tell me the performance of this Query ??. I need to implement this on very High DB of minimum 1M records ? – HaRsH Jun 02 '15 at 10:46
I have a almost similar question [here](http://stackoverflow.com/questions/36978099/how-can-i-check-two-conditions-before-inserting). May you please take a look at it? thx – stack May 02 '16 at 14:10
1

This is really an awesome answer – Himalaya Garg May 15 '16 at 13:04
What if I need this in several columns? – Anfelipe Dec 15 '16 at 15:41
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.D 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

MySQL Join two tables with comma separated values

2 Answers2

Linked

Related