Page 1 of 1

Complex JOIN

Posted: Thu Sep 09, 2010 8:59 am
by jip
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.

Code: Select all

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.

Any suggestions will be greatly appreciated.

Re: Complex JOIN

Posted: Thu Sep 09, 2010 9:37 am
by VladSun

Re: Complex JOIN

Posted: Thu Sep 09, 2010 10:00 am
by jip
Correct, it is MySQL. I found the solution.

Code: Select all

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

Re: Complex JOIN

Posted: Thu Sep 09, 2010 10:30 am
by mikosiko
good.... for kick-start :wink:

Re: Complex JOIN

Posted: Thu Sep 09, 2010 3:23 pm
by VladSun
mikosiko wrote:good.... for kick-start :wink:
Thanks :twisted:

Re: Complex JOIN

Posted: Thu Sep 09, 2010 3:34 pm
by mikosiko
PM in your way Vladsun