FULL OUTER JOIN problem - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

FULL OUTER JOIN problem

Author

Topic

calvinfoo
Posting Yak Master

129 Posts

Posted - 2011-08-05 : 05:50:15

Dear Gurus,

I am trying to combine a Holiday table with Leave table to make a notice board showing who and when is on leave, and the pre defined holidays in between.

I have two tables:
1. Holidays [HolidayDate, HolidayDesc].
A table with all pre defined holidays

2. Leaves [Leave_Date, Requestor, LeaveType, LeaveStatus]
A table stored with all employee leave requests.

Note:
LeaveType = Annual Leave, Sick Leave, Unpaid Leave etc...
LeaveStatus = Draft, Submitted, Approved etc... 3 = Approved.

I use the following SQL, it works fine, but I need to add a filter the Leave Table to ensure the Noticeboard shows only the approved leaves (LeaveStatus = 3). I added

WHERE L.LeaveStatus = 3

, but the result filtered even all the Holiday Table too. Please advise

SELECT     ISNULL(H.HolidayDate, L.Leave_Date) AS noticeDate, L.RequestorInitial, L.RequestorName, L.LeaveTypeDesc, 
                      ISNULL(H.HolidayDesc, L.Remark) AS noticeRemark
FROM         dbo.Holidays AS H FULL OUTER JOIN
                      dbo.view_Leaves AS L ON H.HolidayDate = L.Leave_Date
WHERE L.LeaveStatus = 3
ORDER BY noticeDate

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-05 : 05:52:34

move the condition to the JOIN part


FROM         dbo.Holidays AS H FULL OUTER JOIN
                      dbo.view_Leaves AS L ON  H.HolidayDate = L.Leave_Date
                                           AND L.LeaveStatus = 3

KH
[spoiler]Time is always against us[/spoiler]

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-08-05 : 05:52:49

Move the condition into the JOIN.


SELECT     ISNULL(H.HolidayDate, L.Leave_Date) AS noticeDate, L.RequestorInitial, L.RequestorName, L.LeaveTypeDesc, 
                      ISNULL(H.HolidayDesc, L.Remark) AS noticeRemark

FROM       dbo.Holidays AS H
           FULL OUTER JOIN dbo.view_Leaves AS L ON
                    H.HolidayDate = L.Leave_Date
                    AND L.LeaveStatus = 3

ORDER BY noticeDate

You really want a *full* outer join? not a left join?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-08-05 : 05:53:24

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-05 : 05:54:26

LOL. Almost identitcal

KH
[spoiler]Time is always against us[/spoiler]

calvinfoo
Posting Yak Master

129 Posts

Posted - 2011-08-05 : 06:00:25

Thanks khtan and Charlie. It works!

@Charlie, yes, I need a full join, because I want to make the homepage of eLeave as a noticeboard, just to show who is currently/planned out of office, and slot in holidays in between as a reminder.

calvinfoo
Posting Yak Master

129 Posts

Posted - 2011-08-05 : 06:06:10

khtan,

LOL... it is not "almost" identical, it "IS" identical. Both of you are really God-of-SQL. :) thanks again :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources