Avatar of rdgit
rdgit
 asked on

SQL Select from 2 tables

I have a sql query question.
I have 2 tables... Projects and ProjComments

Projects has 2 fields ProjID and ProjName
ProjComments has 4 fields. ProjCommentID, ProjID, CommentDate and Comment

There can be several comments on one project

what sql code would I use to select all projects and only the most recent
comment for each project? Some projects won't have any comments.

so my results would include
ProjID, ProjName,  comment Date and Comment.

thanks!
Microsoft SQL Server 2005Microsoft SQL ServerMicrosoft SQL Server 2008

Avatar of undefined
Last Comment
rdgit

8/22/2022 - Mon
Guy Hengel [angelIII / a3]

ASKER CERTIFIED SOLUTION
Patrick Matthews

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Scott Pletcher

SELECT
    p.ProjID, p.ProjName, pc.CommentDate, pc.Comment
FROM Projects p
LEFT OUTER JOIN (
    SELECT ProjID, CommentDate, Comment,
        ROW_NUMBER() OVER(PARTITION BY ProjID ORDER BY CommentDate DESC) AS row_num
    FROM ProjComments
) AS pc ON
    pc.row_num = 1 AND
    pc.ProjID = p.ProjID
--ORDER BY p.ProjID | ORDER BY p.ProjName
rdgit

ASKER
Thank you this worked well
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy