JOIN in SQL with one-to-many relationship

Question

I'm making a tool to track calls to house/senate reps, and I have 2 tables of importance here:

reps

rep_id
rep_name # and more info

comments

rep_id
status # enum about result of contact
comment

I want to query for all reps joining the most recent associated comments and in some cases joining comments of a specific status, but there might not be any comments associated with that rep yet.

THANKS!

Answer 1

EDIT:

Use a left join to get all reps,

SELECT reps.rep_id, comments.comment
FROM reps
LEFT JOIN comments
ON reps.rep_id=comments.rep_id

Answer 2

you need some way to distinguish latest comment, so I made up a new column: comments.commentDate, with that or some auto number/identity use a query like this:

SELECT
    r.*,c.*
    FROM reps r
        LEFT OUTER JOIN (SELECT
                             rep_id,MAX(commentDate) AS MaxDate
                             FROM comments
                             GROUP BY rep_id
                        ) m On r.rep_id=m.rep_id
        LEFT OUTER JOIN comments c ON r.rep_id=c.rep_id AND m.MaxDate=c.commentDate
    ORDER BY r.rep_name