Problem with SQL Join

Question

I have two tables, tblEntities and tblScheduling.

tblEntities:

EntityID  ShortName          Active
1         Dirtville          1
2         Goldtown           1
3         Blackston          0
4         Cornfelt           1
5         Vick               1

tblScheduling:

ScheduleID EntityID SchedulingYearID
1          1        20
2          1        21
3          2        20
4          3        19
5          5        20

I need a query that will show ALL ACTIVE Entities and their schedule information for a particular ScheduleYearID.

Output should look like (the desired SchedulingYearID in this case is 20):

EntityID ScheduleID
1        1
2        3
4        NULL
5        5

The query that I have written so far is:

SELECT     tblEntities.EntityID, tblEntities.ShortName, tblScheduling.ScheduleID
FROM         tblScheduling RIGHT OUTER JOIN
                      tblEntities ON tblScheduling.EntityID = tblEntities.EntityID
WHERE     (tblScheduling.SchedulingYearID = @SchedulingYearID) 
AND (tblEntities.Active = 1)
ORDER BY tblEntities.EntityID

My problem is that using this query it will not include active entities without schedule information (such as EntityID 4 in the example above). I can write the query to display all active entities and their schedule status fine, but once I start limiting it via the SchedulingYearID I lose those particular entities.

Are there any solutions that I am obviously missing without having to resort to subqueries, cursors, etc.? If not it's not a big deal, I just feel like I am missing something simple here.

Answer 1

Try this... Join conditions are evaluated to produce the intermediate Join result set, and then, (for an outer join), all the rows from the "Outer" side are added back in before moving on... Where conditions are evaluated after all joins are done...

SELECT E.EntityID, E.ShortName, S.ScheduleID
FROM  tblEntities E 
     Left Join tblScheduling S 
        ON S.EntityID = E.EntityID
           And S.SchedulingYearID = @SchedulingYearID 
WHERE E.Active = 1
ORDER BY E.EntityID

I change your join order cause I prefer left joins... but it doesn't matter

Answer 2

It's your conditions in the where clause: (tblScheduling.SchedulingYearID = @SchedulingYearID)

when there is no tblScheduling info this wil always fail. Add

(((tblScheduling.SchedulingYearID = @SchedulingYearID) OR (tblScheduling.SchedulingYearID is null) )

or wathever null condition checking your DB uses.

Answer 3

I think the trouble is that the WHERE clause is filtering out the rows where SchedulingYearID is null. So don't.

SELECT tblEntities.EntityID, tblEntities.ShortName, tblScheduling.ScheduleID
    FROM tblScheduling RIGHT OUTER JOIN
         tblEntities ON tblScheduling.EntityID = tblEntities.EntityID
    WHERE (tblScheduling.SchedulingYearID = @SchedulingYearID OR
           tblScheduling.SchedulingYearID IS NULL) 
      AND (tblEntities.Active = 1)
    ORDER BY tblEntities.EntityID;