mysql按逗号拼接起来_MySQL用逗号分隔值连接两个表

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.

$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!

解决方案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