Duplicates with multi table joins

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Duplicates with multi table joins

Author

Topic

NickC
Yak Posting Veteran

68 Posts

Posted - 2010-05-24 : 06:42:36

Hello

I am trying to create a query for an MI Report but struggling. This in its current state is pulling in duplicates of i.insurance_ref when I only want it to display one - I understand it could be because other fields are different but I only need the max() or if not possible then just one of them.

This is what I have so far:
select distinct
i.insurance_ref as Policy_Number,
p.name as Agent_Name, ad.address1 as Address_1, ad.address2 as Address_2, ad.address3 as Address_3, ad.address4 as Address_4, ad.postal_code as Postal_Code, c.area_code as Area_Code,
c.number as Number, c.extension as Extension,
sp.first_applicant_title as Title, sp.first_applicant_forename as Forename, sp.first_applicant_surname as Surname,
i.cover_start_date as Cover_Start_Date,
i.renewal_date as Renewal_Date,
fs.date_created as System_Date_Created,
sp.product_variant as Product_Variant,
i.annual_premium as Annual_Premium, i.this_premium as Monthly_Premium,
ref.description as Referral_Description

from plusone_general sp

inner join plusone_policy_binder spb
on sp.plusone_policy_binder_id = spb.plusone_policy_binder_id
inner join gis_policy_link pl
on spb.plusone_policy_binder_id = pl.gis_policy_link_id
inner join risk r
on pl.risk_id = r.risk_cnt
inner join insurance_file_risk_link rl
on r.risk_cnt = rl.risk_cnt
inner join insurance_file i
on rl.insurance_file_cnt = i.insurance_file_cnt

inner join insurance_file_type t
on i.insurance_file_type_id = t.insurance_file_type_id
inner join insurance_file_system fs
on i.insurance_file_cnt = fs.insurance_file_cnt
inner join party p
on i.lead_agent_cnt = p.party_cnt
inner join party_address_usage pau
on p.party_cnt = pau.party_cnt
inner join address ad
on pau.address_cnt = ad.address_cnt
inner join party_contact_usage pcu
on p.party_cnt = pcu.party_cnt
inner join contact c
on pcu.contact_cnt = c.contact_cnt
inner join Plusone_referrals ref
on spb.plusone_policy_binder_id = ref.plusone_policy_binder_id

where i.cover_start_date = '2009-01-01' AND i.renewal_date = '2009-02-01'

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2010-05-24 : 07:59:31

Use ROW_NUMBER() function.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources