CODEWITHSUNDEEP
sqlpostgresql
Dougui
Dougui

Reputation: 7232

How to have a single result with a join?

With postgres, I wrote this SQL Request :

SELECT projects.id
     , projects.title
     , comments.message as comment_message 
FROM "projects" 
RIGHT OUTER JOIN comments 
ON comments.project_id = projects.id
GROUP BY projects.id, comments.message

And I have this kind of result :

 id |     title      | comment_message 
----+----------------+-----------------
  6 | simple project | simple comment
  6 | simple project | simple message

Is it possible to have only the first result? I just want to have one result by project.

Thanks!

Upvotes: 0

Views: 45

Answers (1)

ruakh
ruakh

Reputation: 183280

You can write:

SELECT projects.id,
       projects.title,
       MIN(comments.message) AS comment_message 
  FROM "projects"
 RIGHT
 OUTER
  JOIN comments
    ON comments.project_id = projects.id
 GROUP
    BY projects.id
;

Upvotes: 1

Related Questions