spGetFundRaiserSolicitorContactActionsParentChild

CREATE PROCEDURE dbo.spGetFundRaiserSolicitorContactActionsParentChild
(@FundRaiserSolicitorContactID int)
AS
select a.id as ParentActionID, a.type as ParentActionType, a2.id as ChildActionID, a2.type as ChildActionType
from FundraiserSolicitorContactAction a
left outer join emailjob_recipient as r on r.EmailRecipientID = a.EmailRecipientID
left outer join FundraiserSolicitorContactAction a2 on a2.EmailJobRecipientID = r.ID
where a.FundraiserSolicitorContactID = @FundRaiserSolicitorContactID 
AND
a.emailjobrecipientid is null
ORDER BY a.id,a.CreateDate ASC