title: How to append CTE results to main query output?
body: <p>I've created a TSQL query that pulls from two sets of tables in my database.  The tables in the Common Table Expression are different from the tables in the main query.  I'm joining on MRN and need the end result to contain accounts from both sets of tables.  I've written the following query to this end:</p>
  
  <pre><code>with cteHosp as(
  select Distinct p.EncounterNumber, p.MRN, p.AdmitAge
  from HospitalPatients p
  inner join Eligibility e on p.MRN = e.MRN
  inner join HospChgDtl c on p.pt_id = c.pt_id
  inner join HospitalDiagnoses d on p.pt_id = d.pt_id
  where p.AdmitAge &gt;=12
      and d.dx_cd in ('G89.4','R52.1','R52.2','Z00.129')
  )
  Select Distinct  a.AccountNo,  a.dob, DATEDIFF(yy, a.dob, GETDATE()) as Age
  from RHCCPTDetail c
  inner join RHCAppointments a on c.ClaimID = a.ClaimID
  inner join Eligibility e on c.hl7Id = e.MRN
  full outer join cteHosp on e.MRN = cteHosp.MRN
  where DATEDIFF(yy, a.dob, getdate()) &gt;= 12
  and left(c.PriDiag,7) in ('G89.4','R52.1','R52.2', 'Z00.129')
  or (
  DATEDIFF(yy, a.dob, getdate()) &gt;= 12
  and LEFT(c.DiagCode2,7) in ('G89.4','R52.1','R52.2','Z00.129')
  )
  or (
  DATEDIFF(yy, a.dob, getdate()) &gt;= 12
  and LEFT(c.DiagCode3,7) in ('G89.4','R52.1','R52.2','Z00.129')
  )
  or (
  DATEDIFF(yy, a.dob, getdate()) &gt;= 12
  and LEFT(c.DiagCode4,7) in ('G89.4','R52.1','R52.2','Z00.129')
  )   
  order by AccountNo
  </code></pre>
  
  <p>How do I merge together the output of both the common table expression and the main query into one set of results?  </p>