Top 1 on Left Join SubQuery

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...

Any ideas?

    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 <> '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 <> '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

From stackoverflow

• It's difficult to understand your question so first I'll throw this out there: does changing your SELECT to SELECT DISTINCT do what you want?

  Otherwise, let me get this straight, you're trying to get your customers' current insurance and previous insurance, but they may actually have many insurances before that, recorded in the [transactions] table? I looked at your SQL for quite a few minutes but can't figure out what it all means, so could you please reduce it down to only the parts that are necessary? Then I'll think about it some more. It sounds to me like you need a GROUP BY somehow, but I can't work it out exactly yet.

  RSolberg : I've added more info below, let me know if this clears it up...

• Couldn't take the time to dig through all your SQL (what a beast!), here's an idea that might make things easier to handle:

  select 
   p.person_id, p.name <and other person columns>,
   (select <current policy columns>
    from pol <and other tables for policy>
    where pol.<columns for join> = p.person_id
    and <restrictions for current policy>),
   (select <previous policy columns>
    from pol <and other tables for policy>
    where pol.<columns for join> = p.person_id
    and <restrictions for previouspolicy>),
   <other columns>
  from person p <and "directly related" tables>
  

  This makes the statement easier to read by separating the different parts into their own subselects, and it also makes it easier to add a "Top 1" in without affecting the rest of the statement. Hope that helps.

• The pattern I normally use for this is:

  SELECT whatever
  FROM person
  LEFT JOIN subtable AS s1
  ON s1.personid = person.personid

  ...

  WHERE NOT EXISTS
  ( SELECT 1 FROM subtable
  WHERE personid = person.personid
  AND orderbydate > s1.orderbydate )

  Which avoids the TOP 1 clause and maybe makes it a little clearer.

  BTW, I like the way you've put this query together in general, except I'd leave out the brackets, assuming you have rationally named tables and columns; and you might even gain some performance (but at least elegance) by listing columns for tr1 and tr2, rather than "tr1.*" and "tr2.*".

• Thanks for all of the feedback and ideas...

  In the simplest of terms, I have a person table that stores contact information like name, email, etc. I have another table that stores transactions. Each transaction is really an insurance policy that would contain information on the provider, product type, product name, etc.

  I want to avoid giving the user duplicate person records since this causes them to look for the duplicates prior to running mail merges, etc. I'm getting duplicates when there is more than 1 transaction that has not been terminated, and when there is more than 1 transaction that has been terminated.

  Someone else suggested that I consider a cursor to grab my distinct contact records and then perform the sub selects to get the current and previous insurance information. I don't know if I want to head down that path though.

  le dorfier : With SQL Server, don't even imagine using a cursor. Don't.