Help with a WHERE on a LEFT JOIN SQL Query

Question

I'm trying to construct a query that will include a column indicating whether or not a user has downloaded a document. I have a table called HasDownloaded with the following columns: id, documentID, memberID. Finding out whether a user has downloaded a specific document is easy; but I need to generate a query where the results will look like this:

name              id ---------------------- abc               NULL bbb               2 ccc               53 ddd               NULL eee               13 

The ID isn't really important; what I'm interested in is whether the document has been downloaded (is it NULL or not).

Here is my query:

SELECT Documents.name, HasDownloaded.id FROM Documents LEFT JOIN HasDownloaded ON HasDownloaded.documentID = Documents.id WHERE HasDownloaded.memberID = @memberID 

The problem is, this will only return values if an entry exists for the specified user in the HasDownloaded table. I'd like to keep this simple and only have entries in HasDownloaded for documents that have been downloaded. So if user 1 has downloaded abc, bbb, and ccc, I still want ddd and eee to show up in the resulting table, just with the id as NULL. But the WHERE clause only gives me values for which entries exists.

I'm not much of a SQL expert - is there an operator that will give me what I want here? Should I be taking a different approach? Or is this impossible?

Answer 1

Move the condition in the WHERE clause to the join condition.

SELECT Documents.name, HasDownloaded.id FROM Documents LEFT JOIN HasDownloaded ON HasDownloaded.documentID = Documents.id    AND HasDownloaded.memberID = @memberID  

This is necessary whenever you want to refer to a left join-ed table in what would otherwise be the WHERE clause.

Answer 2

WHERE HasDownloaded.memberId IS NULL OR HasDownloaded.memberId = @memberId 

would be the normal way to do that. Some would shorten it to:

WHERE COALESCE(HasDownloaded.memberId, @memberId) = @memberId 

You can, as Matt B. shows, do it in your JOIN condition - but I think that's much more likely to confuse folks. If you don't understand WHY moving it to the JOIN clause works, then I'd strongly suggest staying away from it.