Outer Join with Condition on only one table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Outer Join with Condition on only one table

Author

Topic

jone0497
Starting Member

8 Posts

Posted - 2007-02-01 : 15:56:36

Does anyone know how to apply a condition to only one table when doing an outer join?

I am working on displaying a department schedule in ASP using VBScript. So I need to avoid nested loops in the VBScript and try to limit the result set size to avoid time out errors. My problem is getting all the names to show for a given week. Here is an explanation of the problem I am having using an example:
There are two source tables, tblName and tblSchedule.

tblName has
NID EmpName
23 Barney Rubble
34 Fred Flinstone
35 Wilma Flinstone
36 Betty Rubble

tblSchedule has
NID EmpEvent EventDate WeekID
23 In office 1/28/2007 391
23 In office 1/29/2007 392
23 In office 1/30/2007 392
23 In office 1/31/2007 392
34 out of office 1/24/2007 391
34 out of office 1/25/2007 391
36 in office 1/28/2007 391
36 in office 1/29/2007 392
36 in office 1/30/2007 392

If I do
SELECT tblName.NID, tblName.EmpName, tblSchedule.EmpEvent, tblSchedule.EventDate, tblSchedule.WeekID FROM tblName LEFT OUTER JOIN tblSchedule ON tblSchedule.NID = tblName.NID WHERE tblSchedule.WeekID = 392;
I get:
NID EmpName EmpEvent EventDate WeekID
23 Barney Rubble In office 1/29/2007 392
23 Barney Rubble In office 1/30/2007 392
23 Barney Rubble In office 1/31/2007 392
36 Betty Rubble in office 1/29/2007 392
36 Betty Rubble in office 1/30/2007 392
35 Wilma Flinstone NULL NULL NULL

Fred Flinstone would be missing, because he was in the schedule table under a different WeekID. I need him in there. WeekID will be constantly changing.

If the where clause is applied to tblSchedule prior to the Outer Join, I should be able to get
NID EmpName EmpEvent EventDate WeekID
23 Barney Rubble In office 1/29/2007 392
23 Barney Rubble In office 1/30/2007 392
23 Barney Rubble In office 1/31/2007 392
36 Betty Rubble in office 1/29/2007 392
36 Betty Rubble in office 1/30/2007 392
34 Fred Flinstone NULL NULL NULL
35 Wilma Flinstone NULL NULL NULL

Obviously, this code won't work (but it kinda illustrates the idea):
SELECT tblName.NID, tblName.EmpName, tblSchedule.EmpEvent, tblSchedule.EventDate, tblSchedule.WeekID FROM tblName LEFT OUTER JOIN (SELECT * FROM tblSchedule WHERE tblSchedule.WeekID = 392) ON tblSchedule.NID = tblName.ID

Thanks,
JJ

nr
SQLTeam MVY

12543 Posts

Posted - 2007-02-01 : 16:07:02

SELECT tblName.NID, tblName.EmpName, tblSchedule.EmpEvent, tblSchedule.EventDate, tblSchedule.WeekID FROM tblName LEFT OUTER JOIN tblSchedule ON tblSchedule.NID = tblName.NID and tblSchedule.WeekID = 392;

if you reference the outer join table in a where clause it turns the query into an inner join.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jone0497
Starting Member

8 Posts

Posted - 2007-02-01 : 16:30:05

Thank you for the insight. That appears to have fixed the problem.

Best Wishes
-JJ

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources