title: Top 1 on Left Join SubQuery
body: <p>I am trying to take a person and display their current insurance along with their former insurance.  I guess one could say that I'm trying to flaten my view of customers or people.  I'm running into an issue where I'm getting multiple records back due to multiple records existing within my left join subqueries.  I had hoped I could solve this by adding "TOP 1" to the subquery, but that actually returns nothing...</p>
  
  <p>Any ideas?</p>
  
  <pre><code>    SELECT 
      p.person_id AS 'MIRID'
      , p.firstname AS 'FIRST'
      , p.lastname AS 'LAST'
      , pg.name AS 'GROUP'
      , e.name AS 'AOR'
      , p.leaddate AS 'CONTACT DATE'
      , [dbo].[GetPICampaignDisp](p.person_id, '2009') AS 'PI - 2009'
      , [dbo].[GetPICampaignDisp](p.person_id, '2008') AS 'PI - 2008'
      , [dbo].[GetPICampaignDisp](p.person_id, '2007') AS 'PI - 2007'
      , a_disp.name AS 'CURR DISP'
      , a_ins.name AS 'CURR INS'
      , a_prodtype.name AS 'CURR INS TYPE'
      , a_t.date AS 'CURR INS APP DATE'
      , a_t.effdate AS 'CURR INS EFF DATE' 
      , b_disp.name AS 'PREV DISP'
      , b_ins.name AS 'PREV INS'
      , b_prodtype.name AS 'PREV INS TYPE'
      , b_t.date AS 'PREV INS APP DATE'
      , b_t.effdate AS 'PREV INS EFF DATE'
      , b_t.termdate AS 'PREV INS TERM DATE'
  FROM 
      [person] p
  LEFT OUTER JOIN 
      [employee] e
  ON 
      e.employee_id = p.agentofrecord_id
  INNER JOIN 
      [dbo].[person_physician] pp
  ON 
      p.person_id = pp.person_id
  INNER JOIN 
      [dbo].[physician] ph
  ON
      ph.physician_id = pp.physician_id
  INNER JOIN
      [dbo].[clinic] c
  ON 
      c.clinic_id = ph.clinic_id
  INNER JOIN
      [dbo].[d_Physgroup] pg
  ON
      pg.d_physgroup_id = c.physgroup_id
  LEFT OUTER JOIN 
      (
          SELECT
              tr1.*
          FROM 
              [transaction] tr1
          LEFT OUTER JOIN 
              [d_vendor] ins1
          ON 
              ins1.d_vendor_id = tr1.d_vendor_id
          LEFT OUTER JOIN 
              [d_product_type] prodtype1
          ON 
              prodtype1.d_product_type_id = tr1.d_product_type_id
          LEFT OUTER JOIN 
              [d_commission_type] ctype1
          ON 
              ctype1.d_commission_type_id = tr1.d_commission_type_id
          WHERE
              prodtype1.name &lt;&gt; 'Medicare Part D'
              AND tr1.termdate IS NULL
      ) AS a_t
  ON
      a_t.person_id = p.person_id
  LEFT OUTER JOIN 
      [d_vendor] a_ins
  ON 
      a_ins.d_vendor_id = a_t.d_vendor_id
  LEFT OUTER JOIN 
      [d_product_type] a_prodtype
  ON 
      a_prodtype.d_product_type_id = a_t.d_product_type_id
  LEFT OUTER JOIN 
      [d_commission_type] a_ctype
  ON 
      a_ctype.d_commission_type_id = a_t.d_commission_type_id
  LEFT OUTER JOIN
      [d_disposition] a_disp
  ON
      a_disp.d_disposition_id = a_t.d_disposition_id
  LEFT OUTER JOIN 
      (
          SELECT
              tr2.*
          FROM 
              [transaction] tr2
          LEFT OUTER JOIN 
              [d_vendor] ins2
          ON 
              ins2.d_vendor_id = tr2.d_vendor_id
          LEFT OUTER JOIN 
              [d_product_type] prodtype2
          ON 
              prodtype2.d_product_type_id = tr2.d_product_type_id
          LEFT OUTER JOIN 
              [d_commission_type] ctype2
          ON 
              ctype2.d_commission_type_id = tr2.d_commission_type_id
          WHERE
              prodtype2.name &lt;&gt; 'Medicare Part D'
              AND tr2.termdate IS NOT NULL
      ) AS b_t
  ON
      b_t.person_id = p.person_id
  LEFT OUTER JOIN 
      [d_vendor] b_ins
  ON 
      b_ins.d_vendor_id = b_t.d_vendor_id
  LEFT OUTER JOIN 
      [d_product_type] b_prodtype
  ON 
      b_prodtype.d_product_type_id = b_t.d_product_type_id
  LEFT OUTER JOIN 
      [d_commission_type] b_ctype
  ON 
      b_ctype.d_commission_type_id = b_t.d_commission_type_id
  LEFT OUTER JOIN
      [d_disposition] b_disp
  ON
      b_disp.d_disposition_id = b_t.d_disposition_id
  WHERE
      pg.d_physgroup_id = @PhysGroupID
  </code></pre>