Right Outer Join Not working - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Right Outer Join Not working

Author

Topic

nitsmooth
Yak Posting Veteran

68 Posts

Posted - 2011-09-23 : 05:01:42

The Join below does not give me the Dates of the Timetable for which there are no records in the time sheet? But i want those rows also. How can i change this query? I tried to put the condition < OR tt.timeid IS NULL > in where clause also. It does show me the Null rows of timesheet , but then the dates get out of range?

select * from timesheet tt 
right outer  join timetable tb on tt.timeid = tb.timeid 
where employeeid = xxx And  tb.Date_Name between cast('2011-08-21 00:00:00' as Datetime) and cast('2011-09-21 00:00:00' as Datetime))
order by tb.timeid

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-23 : 05:26:12

which table has employeeid?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-23 : 05:26:55

i think this might do the trick


select * from timesheet tt 
right outer  join timetable tb on tt.timeid = tb.timeid and employeeid = xxx 
where   tb.Date_Name between cast('2011-08-21 00:00:00' as Datetime) and cast('2011-09-21 00:00:00' as Datetime))
order by tb.timeid

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nitsmooth
Yak Posting Veteran

68 Posts

Posted - 2011-09-23 : 06:49:06

Absolutely that did the trick! :-)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-23 : 06:51:29

welcome
If you're curious on reason for this see below link

http://weblogs.sqlteam.com/jeffs/archive/2007/05/14/criteria-on-outer-joined-tables.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nitsmooth
Yak Posting Veteran

68 Posts

Posted - 2011-09-23 : 06:59:48

Thanks much!! Visakh :-)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-23 : 07:28:41

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources