Complex JOIN
Posted: Thu Sep 09, 2010 8:59 am
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.
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.
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
Any suggestions will be greatly appreciated.