SQL Join problem
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 display ALL ACTIVE Entities and their schedule information for a specific ScheduleYearID.
The output should look like this (the desired SchedulingYearID is 20 in this case):
EntityID ScheduleID
1 1
2 3
4 NULL
5 5
The request I've 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 won't include active entities without schedule information (like EntityID 4 in the example above). I can write a query to display all active objects and their graph status is ok, but as soon as I start to constrain it with SchedulingYearID, I lose those specific objects.
Are there any solutions I am obviously missing without resorting to subqueries, cursors, etc.? If not, I just feel like I am missing something simple here.
source to share
Try this ... Join conditions are evaluated to create an intermediate join result set and then (for an outer join) all rows from the "Outer" side are appended back before moving ... Where conditions are evaluated after all joins are complete ...
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 the join order, I prefer left joins ... but that doesn't matter.
source to share
These are your conditions in the where clause: (tblScheduling.SchedulingYearID = @SchedulingYearID)
when there is no tblScheduling info, this wil always fail. Add to
(((tblScheduling.SchedulingYearID = @SchedulingYearID) OR (tblScheduling.SchedulingYearID is null))
or a null state, which checks the usage of your DB.
source to share
I think the problem is that the WHERE clause is filtering for rows where the SchedulingYearID is null. So don't do it.
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;
source to share