1

I have a query that use left outer join.

something like that :

select issues.id, journal.notes from issues left outer join on (issues.id = journal.issue_id and journal.notes != '')

So far it works.

But in fact in the database journal there can be multiple row that reference the issues one.

So for each row of journal, it duplicates the result of issues.

What I would like to do is merge notes row for a same issue.

Example :

issues : id 1 2

journal : issue_id, notes 1, note 1 2, note 1 for issue 2 2, note 2 for issue 2

Actually it returns this result :

1, note 1 2, note 1 for issue 2 2, note 2 for issue 2

And I would like it to be :

1, note 1 2, note 1 for issue 2\nnote 2 for issue 2

I am using postgres.

How can I do this kind of merge ?

Thanks.

1

Perhaps you are looking for this:

select issues.id, array_agg(journal.notes) from issues 
left outer join journal on (issues.id = journal.issue_id and journal.notes != '')
group by issues.id

Please check this http://sqlfiddle.com/#!1/24db9/2

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.