SELECT and Left Outer Join with Conditions

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)

SELECT and Left Outer Join with Conditions

Author

Topic

dprichard
Yak Posting Veteran

94 Posts

Posted - 2006-09-21 : 10:48:36

I have a SELECT statement with a LEFT OUTER JOIN that joins the employee and request tables together. I am using a LEFT OUTER JOIN because there may not be any results in the request table. It works fine, but then I try to specify two conditions on the request table and it gives me a null result.

WHERE      (employee.emp_id = @emp_id) AND (request.requested_time_taken = 'FALSE') AND (request.request_end_date >= GETDATE()))

Here is the complete SELECT Statement:

SELECT     (SELECT     SUM(ISNULL(request.request_duration, '0')) AS Expr1
                       FROM          employee LEFT OUTER JOIN
                                              request AS request ON employee.emp_id = request.emp_id
                       WHERE      (employee.emp_id = @emp_id) AND (request.requested_time_taken = 'FALSE') AND (request.request_end_date >= GETDATE())) 
                      AS dayspending
FROM         employee AS employee_1 LEFT OUTER JOIN
                      request AS request_1 ON employee_1.emp_id = request_1.emp_id
WHERE     (employee_1.emp_id = @emp_id)
GROUP BY employee_1.emp_id, employee_1.emp_begin_accrual, employee_1.emp_accrual_rate, employee_1.emp_fname, employee_1.emp_minitial, 
                      employee_1.emp_lname

Any help on this would be greatly appreciated.

Thank you!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-21 : 10:53:21

place the condition in the JOIN part not WHERE


FROM  employee AS employee_1 LEFT OUTER JOIN request AS request_1 
ON    employee_1.emp_id = request_1.emp_id
AND   request_1.requested_time_taken = 'FALSE'
AND   request_1.request_end_date >= GETDATE()

dprichard
Yak Posting Veteran

94 Posts

Posted - 2006-09-21 : 11:10:33

You ROCK!!!

Thanks... Been banging my head trying to figure out this one!!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources