LEFT JOIN not working 1

[organicg]

Seemingly simple LEFT JOIN, but the results look as if an INNER JOIN is actually being done, but I don't know why.
Table1:Event (EventID, EventDate,...)
Table2:Event_Signups(EmployeeID, EventID, NumOfTickets)

SELECT  e.EventID, e.EventDate, 
	es.EmployeeID, es.NumOfTickets,

FROM Event e LEFT JOIN Event_Signups es
ON e.EventID = es.EventID
WHERE e.[Date] BETWEEN '2009-08-01' AND '2009-08-31'
AND es.EmployeeID = 123

Doing LEFT JOIN because I want all the events for this month even if no employees have requested tickets.

 

[gmmastros]

SELECT e.EventID, e.EventDate,
       es.EmployeeID, es.NumOfTickets,
FROM   Event e 
       LEFT JOIN Event_Signups es
         ON  e.EventID = es.EventID
         AND es.EmployeeID = 123
WHERE e.[Date] BETWEEN '2009-08-01' AND '2009-08-31'

A left join will return rows from the "right" table that do not exist in the left table. In your case, the es table may not have a matching row (matching on EventId). If there is no match, then you still get the row, but all the columns in the right table (es) will have a value NULL.

Now, when you put a condition in the where clause on the right table (employeeid=123), the NULL values will not match, and the row will be filtered out. By putting that condition in the ON clause, the data will still be returned.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom