I have a the following query that ends up returning two records for the same file, provided they have 2 comments associated with them (i.e., one record with multiple comments).
A given file can have more than one comment assigned to it, which is why this query will return two records for the same file provided there are multiple comments assigned.
SELECT t1.filename, t2.comment FROM proposal_files as t1
LEFT JOIN prop_file_version_comments as t2 ON t1.id = t2.fileid
WHERE t1.propid = ‘202’
ORDER BY t1.filename ASC
I need to perform some sort of a CONCAT function so that query returns a single record but combines all comments into a single comma-separated field.
Here are the respective tables:
CREATE TABLE IF NOT EXISTS proposal_files (
id int(10) unsigned NOT NULL auto_increment,
propid int(10) unsigned NOT NULL default ‘0’,
filename varchar(255) NOT NULL default ‘’,
inout int(10) unsigned NOT NULL default ‘0’,
folder int(10) unsigned NOT NULL default ‘0’,
process int(10) unsigned NOT NULL default ‘0’,
userid varchar(100) NOT NULL default ‘’,
owner varchar(100) NOT NULL default ‘’,
filesize varchar(50) default NULL,
pagecount int(10) unsigned default NULL,
created datetime NOT NULL default ‘0000-00-00 00:00:00’,
last_updated datetime NOT NULL default ‘0000-00-00 00:00:00’,
PRIMARY KEY (id)
CREATE TABLE IF NOT EXISTS prop_file_version_comments (
id int(10) unsigned NOT NULL auto_increment,
fileid int(10) unsigned NOT NULL default ‘0’,
current_version int(1) unsigned NOT NULL default ‘0’,
comment blob NOT NULL,
commenter varchar(100) NOT NULL default ‘’,
timestamp datetime NOT NULL default ‘0000-00-00 00:00:00’,
PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=119 ;
Any suggestions will be greatly appreciated.