Complex JOIN Query

[JipThePeople]

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.

[wildteen88]

Look into using the GROUP BY clause and the GROUP_CONCAT function.

[mikosiko]

adding to what wildteen88 said....

 

pay attention to group_concat_max_len (1024 by default)... but can be adjusted if neccesary

[JipThePeople]

Thx for the replies. I am struggling with the syntax. Could please provide an example?

[kickstart]

Hi

 

Just something like

 

SELECT t1.filename, GROUP_CONCAT(t2.comment)
FROM proposal_files as t1
LEFT JOIN prop_file_version_comments as t2 ON t1.id = t2.fileid
WHERE t1.propid = '202'
GROUP BY t1.filename
ORDER BY t1.filename ASC

 

All the best

 

Keith

[JipThePeople]

Hi

 

Just something like

 

SELECT t1.filename, GROUP_CONCAT(t2.comment)
FROM proposal_files as t1
LEFT JOIN prop_file_version_comments as t2 ON t1.id = t2.fileid
WHERE t1.propid = '202'
GROUP BY t1.filename
ORDER BY t1.filename ASC

 

All the best

 

Keith

 

That's the solution. Thanks for your time!