Help with SQL Join on two tables

Question

I have two tables, one is a table of forum threads. It has a last post date column.

Another table has PostID, UserId, and DateViewed.

I want to join these tables so I can compare DateViewed and LastPostDate for the current user. However, if they have never viewed the thread, there will not be a row in the 2nd table.

This seems easy but I cant wrap my head around it. Advice please.

Thanks in advance.

Answer 1

So you're thinking something like:

SELECT t.UserID, t.PostID, t.LastPostDate, v.DateViewed
FROM dbo.Threads t
LEFT JOIN dbo.Views v ON v.PostID = t.PostID
                     AND v.UserID = t.UserID
WHERE t.UserID = @user;

v.DateViewed will be NULL if there's no corresponding row in Views.

If you have lots of rows in Views, you may prefer to do something like:

SELECT t.UserID, t.PostID, t.LastPostDate, v.DateViewed
FROM dbo.Threads t
CROSS APPLY (SELECT MAX(vw.DateViewed) as DateViewed 
             FROM dbo.Views vw
             WHERE vw.PostID = t.PostID
               AND vw.UserID = t.UserID
            ) v
WHERE t.UserID = @user;

Answer 2

The key is to use a LEFT JOIN, which will cause non-existent rows on the right side to come up as all NULL:

SELECT threads.lastpostdate, posts.dateviewed
FROM threads
LEFT JOIN posts
  ON threads.id=posts.postid

Answer 3

What is it that you're trying to do specifically - determine if there are unread posts?

You just need to use an outer join:

SELECT p.PostID, p.LastPostDate, ...,
    CASE
        WHEN v.DateViewed IS NULL OR v.DateViewed < p.LastPostDate THEN 1
        ELSE 0
    END AS Unread
FROM Posts p
LEFT JOIN PostViews v
    ON v.PostID = p.PostID
    AND v.UserID = @UserID

Note that I've placed the UserID test in the JOIN condition; if you put it in the WHERE predicate then you'll get no results because there will be no matching rows in the PostViews table.