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.