I have 4 tables:

Project: id name

Keyword: id, name

ProjectKeyword: project_id, keyword_id (project has many keywords, keywords has many projects)

ProjectReport: project_id, keyword_id, position(keyword, project has many project_reports)

ProjectCompetitorReport: project_report, keyword_id, position, competitor_idposition`(keyword, project, competitor has many project_reports)

With the help of those tables I need to produce something like this output:

Keyword.name, project_report.position, project_competitor_report.position

The problem that I have is repetition, I don't now how to group the result.

    project_keywords p1
    keywords k on p1.keyword_id = k.id
    project_reports pr1 on
        p1.project_id = pr1.project_id AND p1.keyword_id = pr1.keyword_id
    project_competitor_reports pr2 on
        p1.project_id = pr2.project_id AND p1.keyword_id = pr2.keyword_id;

I need to group the result produced by that query so that for each keyword i get all the positions from project_reports and all positions from project_position_reports.

As an exemple: keyword1 has position with value: 1 and 2 on project_report keyword1 has position whth value: 2 on project_competitor_reports

For this the output should be:

keyword1 1,2 2

At the moment I get them like this:

keyword1 1, 2

keyword1 2, 2

1 Answer 1


Use array_agg or string_agg

    array_agg(pr.position order by pr.position) as pr_position,
    array_agg(pcr.position order by pcr.position) as pcr_position
    project_keywords pk
    inner join
    keywords k on pk.keyword_id = k.id
    inner join
    project_reports pr using (project_id,  keyword_id)
    inner join
    project_competitor_reports pcr using (project_id, keyword_id)
group by k.name
  • There will be keywords having the position 0, meaning that they will not be stored in the DB on project_report, or project_competitor_reports what do I need to change to capture those keywords as well? Thank you for your time.
    – RockNinja
    Mar 26, 2014 at 17:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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