title: MySQL Join two tables with comma separated values
body: <p>I have 2 tables as below</p>
  
  <p>Notes Table</p>
  
  <pre><code>╔══════════╦═════════════════╗
  ║ nid      ║    forDepts     ║
  ╠══════════╬═════════════════╣
  ║ 1        ║ 1,2,4           ║
  ║ 2        ║ 4,5             ║
  ╚══════════╩═════════════════╝
  </code></pre>
  
  <p>Positions Table</p>
  
  <pre><code>╔══════════╦═════════════════╗
  ║ id       ║    name         ║
  ╠══════════╬═════════════════╣
  ║ 1        ║ Executive       ║
  ║ 2        ║ Corp Admin      ║
  ║ 3        ║ Sales           ║
  ║ 4        ║ Art             ║
  ║ 5        ║ Marketing       ║
  ╚══════════╩═════════════════╝
  </code></pre>
  
  <p>I am looking to query my Notes table and associate the 'forDepts' column with values from the Positions table.</p>
  
  <p>The output should be:</p>
  
  <pre><code>    ╠══════════╬════════════════════════════╣
      ║ 1        ║ Executive, Corp Admin, Art ║
      ║ 2        ║ Art, Marketing             ║
      ╚══════════╩════════════════════════════╝
  </code></pre>
  
  <p>I know that the database should be normalized but I cannot change the database structure for this project.</p>
  
  <p>This is going to be used to export a excel file with the code below.</p>
  
  <pre><code>&lt;?PHP
  
      $dbh1 = mysql_connect($hostname, $username, $password); 
      mysql_select_db('exAdmin', $dbh1);
  
  
      function cleanData(&amp;$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' =&gt; "Created For",
                  'Company' =&gt; "Company",
                  'Case ID' =&gt; "Case ID",
                  'Created On' =&gt; "Created On",
                  'Created By' =&gt; "Created By",
                  'Description' =&gt; "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;
  
    ?&gt;
  </code></pre>
  
  <p>This code outputs only the first value of 'forDepts'</p>
  
  <p>Exa: Executive (instead of Executive, Corp Admin, Art)</p>
  
  <p>Can this be accomplished by CONCAT or FIND_IN_SET?</p>
  
  <p>Please help! Thanks in advance!</p>