gistfile1.sql

select t.ProjectId as 'TitleId', t.ProjectName as 'Title', t.FranchiseId as 'FranchiseId', t.LabelId as 'LabelId',
     m.Id as 'milestoneTemplateId', m.MilestoneName as 'MilestoneTemplate',
	   mm.Id as 'MilestoneId', m2.MilestoneName
from campaigntracker.eumarketing_milestones m
cross join campaigntracker.eumarketing_projects t
left outer join campaigntracker.eumarketing_milestonemetadata mm on m.Id = mm.MilestoneId and t.ProjectId = mm.ProjectId
left outer join campaigntracker.eumarketing_milestones m2 on mm.MilestoneId = m2.Id
where m.ParentId is null
  and t.ProjectId = coalesce(@titleid, t.ProjectId)
  and coalesce(t.FranchiseId, -1) = coalesce(@franchiseId, coalesce(t.FranchiseId, -1))
  and coalesce(t.LabelId, -1) = coalesce(@labelId, coalesce(t.LabelId, -1))
  and coalesce(m.GroupId, -1) = coalesce(@milestoneGroupId, coalesce(m.GroupId, -1)) 
  and coalesce(mm.IsActive, 1) = 1
group by t.ProjectId, t.ProjectName, t.FranchiseId, t.LabelId,
		 m.Id, m.MilestoneName,
		 mm.Id, m2.MilestoneName
order by t.ProjectName, m.MilestoneName