Geek Answers Handbook

How to handle empty rows in CROSS APPLY [SQL Server]

I'm below the Stored Procedure -

 ALTER PROCEDURE [dbo].[Optimized_GetArticlePostAMP] ( @PostID int ) AS BEGIN SET NOCOUNT ON; SET STATISTICS TIME ON DECLARE @SectionId int ,@datediff int DECLARE @postdate datetime SELECT P.PostId, P.SectionID, P.PostName,MP.MetaTitle,P.Postdate,P.PostAuthor,P.IsApproved, MP.Metadescription, MP.Metakeywords,ISNULL(MP.IsRobotsMetaTag,0) as IsRobotsMetaTag,p.TotalViews, P.Subject, P.FormattedBody, MV.Isvideo,MV.VideoCode,MV.VideoCaption, A.DrComment,A.SpanishURL, PS.RedirectUrl, Isnull(PS.IsRedirect,0) as IsRedirect, ISNULL(A.CommentedBy,38633) as CommentedBy ,MP.Canonical as Canonical,ISNULL(MP.RRpopUP ,0) as RRpopUP,ISNULL(A.PrevPost,0) as PreviousPostId, ISNULL(A.NextPost,0) as NextPostId,PS.StatusId ,dbo.[mercola_GetCommentCountForPost](@PostId) as TotalReplies, isnull(PA.[FileName],'') as FileName, PRD.StoryImage, PRD.StoryContent, ISNULL(NULLIF(prd.ALT, ''), P.Subject) AS ALT, ISNULL(PR.ReferenceData,'')as ReferenceData, MH.LastModifiedDate, CASE WHEN CHARINDEX('<p><strong>By', FormattedBody, -1)=1 THEN LTRIM(SUBSTRING(REPLACE(CAST(FormattedBody as varchar(max)),'<p><strong>By ',''),0,CHARINDEX('<',REPLACE(cast(FormattedBody as varchar(max)),'<p><strong>By ','')))) ELSE 'Dr.Mercola' END as Name FROM cs_posts P LEFT JOIN Mercola_NewsletterDetails A on (P.Postid = A.postid) LEFT JOIN Mercola_PostStatus PS on (PS.postid=p.postid) LEFT JOIN Mercola_PostMetatags MP on(P.postid=MP.Postid) LEFT JOIN Mercola_postVideo MV on(P.postid=MV.Postid) LEFT JOIN CS_PostAttachments PA on P.PostId=PA.PostId AND PA.contenttype LIKE 'audio/mpeg' AND PA.FILENAME LIKE '%.mp3' AND PA.isremote = 1 LEFT JOIN Mercola_PostRelatedData PRD on P.PostId=PRD.PostId LEFT JOIN Mercola_PostReferences PR on P.PostId=PR.PostId CROSS APPLY (select top 1 LastModifiedDate from Mercola_ArticleModifiedHistory where Mercola_ArticleModifiedHistory.Postid = P.postid order by LastModifiedDate desc)MH where P.Postid = @Postid

Now that I execute above SP with below PostID -

--[Mercola_Optimized_GetArticlePostAMP] 732490 I get the data below expected . Since the request inside cross apply has data for the above PostID .

enter image description here

But now that I execute the same SP with below different PostID -

--[Mercola_Optimized_GetArticlePostAMP] 40702 I get below empty data [rows] . Since the request inside cross apply does not contain data for the above PostID Infact, other joins have data.

enter image description here

Expected Result for the Above Case - Return the data and set the default value for cross apply . How can i do this?

+5
source share
2 answers

Use OUTER APPLY instead of CROSS APPLY

To overflow NULL, use ISNULL(MH.LastModifiedDate, @DefaultValue) as LastModifiedDate

+8
source

Change your request to Outer Apply, which will keep the rows on the left side, even if there are no matches

  outer APPLY (select top 1 LastModifiedDate from ArticleModifiedHistory where ArticleModifiedHistory.Postid = P.postid order by LastModifiedDate desc

Cross apply is similar to Inner Join , so you only get matching rows, Outer apply will be like Left join , which will keep your left table, even if there are no matching rows

update:

if you want to set the default value for the outer row apply in case null, just use IsNull in select

something like below:

 select *,isnull(b.id,'defaultvalue') from test1 t1 outer apply(select id from test2 t2 where t1.id=t2.id) b
+2
source

More articles:


All Articles