Top 1 Left Join SubQuery

Question

I am trying to take a person and show my current insurance along with my previous insurance. I think we can say that I'm trying to talk about clients or people. I ran into a problem when I get multiple records due to multiple records existing in my left join subqueries. I was hoping I could solve this by adding "TOP 1" to the subquery, but it 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 

Answer 1

Usually a template is used for this:

CHOOSE anything from a person
AUXILIARY GIFT GIFTS AS s1
ON s1.personid = person.personid

...

WHERE DOESN'T EXIST
(SELECT 1 FROM subtable
WHERE personid = person.personid
And orderbydate> s1.orderbydate)

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

By the way, I like the way you combined this query as a whole, except that I will not leave brackets, assuming that you rationally named tables and columns; and you can even get some performance (but at least elegance) by specifying the columns for tr1 and tr2, rather than "tr1. *" and "tr2. *".

Answer 2

On Sql 2005 server you can use OUTER APPLY

 SELECT p.person_id, seemployee_id FROM person p OUTER APPLY (SELECT TOP 1 * FROM Employee WHERE /*JOINCONDITION*/ ORDER BY /*Something*/ DESC) s 

http://technet.microsoft.com/en-us/library/ms175156.aspx

Answer 3

Thanks for all the feedback and ideas ...

In the simplest of terms, I have a face table that stores contact information such as name, email address, etc. I have another table in which transactions are stored. Each transaction is really an insurance policy that will contain information about the supplier, type of product, product name, etc.

I want to prevent users from duplicating a person’s records, as this forces them to look for duplicates before starting mail merges, etc. I get duplicates if there is more than one transaction that has not been interrupted, and when there is more than one transaction that has been interrupted.

Someone suggested that I believe that the cursor captures my individual contact records and then performs sub-selections to get current and previous insurance information. I do not know if I want to go this way, though.

Answer 4

It's hard to understand your question, so I will throw this away first: will your SELECT SELECT DISTINCT change what you want?

Otherwise, let me understand this, are you trying to get the current insurance of your clients and the previous insurance, but before that they can have many insurances recorded in the [transaction] table? I looked at your SQL for several minutes, but I can’t understand what all this means, so could you reduce it to the parts that are needed? Then I'll think about it a little more. It seems to me that you need a GROUP BY group, but I still can not parse it.

Answer 5

Could you take the time to dig through all of your SQL (what a beast!), Here is an idea that could simplify processing:

 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 simplifies reading by splitting the various parts into its own subqueries, and also simplifies the addition of "Top 1" without affecting the rest of the instructions. Hope this helps.