CODEWITHSUNDEEP
sqlsql-server
Kalessin
Kalessin

Reputation: 2302

How can I get all records from one table, even if there are no corresponding records in the JOINed table?

I have a table of [Users], simplified for this example:

uID | uName | uSalesRep
----+-------+----------
  1 | John  | 1
  2 | Bob   | 1
  3 | Fred  | 1
  4 | Stu   | 1

And a table of sales [Activity]:

aID | aDate      | aUserID | aText
----+------------+---------+---------------
  1 | 2013-10-09 |       1 | John did stuff
  2 | 2013-10-14 |       2 | Bob did stuff
  3 | 2013-10-17 |       3 | Fred did stuff

I want to get a list of all sales reps, together with their activity for the week beginning 14th October 2013, and here's how I'm trying to do it:

SELECT uID, Name, aID, aDate, aText
FROM [Users]
LEFT JOIN [Activity] ON uID = UserID
WHERE (aDate >= '2013-10-14' OR aDate = NULL)
AND (aDate <= '2013-10-18' OR aDate = NULL)
AND uSalesRep = 1

I used a LEFT JOIN in the hope of retrieving all reps, but I think this is being overridden by the aDate requirements. Including aDate = NULL includes reps with no activity at all, but reps who have activity outside the specified range are being omitted.

How can I get all the reps at least once, regardless of any activity they have recorded?

Thanks for your time.

Upvotes: 1

Views: 99

Answers (1)

podiluska
podiluska

Reputation: 51494

When the filter applies to the joined table, you need to put the filter on the join, not in the where clause

SELECT uID, Name, aID, aDate, aText
FROM [Users]
LEFT JOIN [Activity] ON uID = UserID
AND (aDate >= '2013-10-14')
AND (aDate <= '2013-10-18')
WHERE uSalesRep = 1

Upvotes: 2

Related Questions