CTE cross join with empty recordset

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

CTE cross join with empty recordset

Author

Topic

forwheeler
Starting Member

44 Posts

Posted - 2011-02-01 : 14:07:09

In this query I want to return results even if the cross joined CTE doesn't return any records. The cteLastYear may not return any records because of the date subquery but I want the main query to return results for the Rating. Currently if there is no cteLastYear then the cteCurrent.Rating is null.

with cteCurrent (DevelopmentObjectiveID, Rating, RatingDate)as

(select TOP 1 DevelopmentObjectiveID, Rating, RatingDate
from ParticipantDevelopmentObjective
where ParticipantID = 1
order by RatingDate desc),

cteLastYear as
(select top 1 Rating AS Rating1, RatingDate
from ParticipantDevelopmentObjective
where ParticipantID = 1
and RatingDate < (SELECT RatingDate FROM cteCurrent)
order by RatingDate desc)--date is preventing the data return

select cteLastYear.Rating1, cteCurrent.Rating
from cteCurrent
cross join cteLastYear

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-02-01 : 15:43:35

Change CROSS JOIN to LEFT JOIN and set the ON condition to ON 1 = 1.

N 56°04'39.26"
E 12°55'05.63"

forwheeler
Starting Member

44 Posts

Posted - 2011-02-01 : 17:32:55

Peso
That works good! Thanks for the help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources