Complex JOIN

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
jip
Forum Newbie
Posts: 3
Joined: Thu May 03, 2007 3:58 pm

Complex JOIN

Post 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.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Complex JOIN

Post by VladSun »

There are 10 types of people in this world, those who understand binary and those who don't
jip
Forum Newbie
Posts: 3
Joined: Thu May 03, 2007 3:58 pm

Re: Complex JOIN

Post 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
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Complex JOIN

Post by mikosiko »

good.... for kick-start :wink:
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Complex JOIN

Post by VladSun »

mikosiko wrote:good.... for kick-start :wink:
Thanks :twisted:
There are 10 types of people in this world, those who understand binary and those who don't
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Complex JOIN

Post by mikosiko »

PM in your way Vladsun
Post Reply