CODEWITHSUNDEEP
sqljoin
Mike
Mike

Reputation: 67

Join a table with Already Joined table based on

I am thinking of joining a table with already joined table based on the 'Policy-numbers'. I am using SQL server.

The First part of query is as follows :

     select
            MPolicyNumber,
            SNumber,
            IName,
            EDate,
            NewRenewal,
            GPremiumUSD,
            Status,
            deductibleinUSD
    from IT.dbo.Blended
    where deductibleinUSD > 0 and ProductLine in ('Health','Cas')
    order by EDate

I want to join the above table with the following table based on Policy numbers that is matching between the first and second query

Second Query: 

    select a.[Policy Number], 
    a.[LOB], 
    a.[Primary_R Amount]as Bound_deductibles,
    a.[Primary_R Type],  
    a.[Effective Date] as CAS_EDate
    from IT.dbo.BOUND_TAB a
    inner join IT.dbo.RATER_OF_RECORD b
    on a.idxFile = b.[Bound Rater]
    order by [Policy Number], [idxFile] desc

Thanks in Advance for the help!

Upvotes: 0

Views: 4178

Answers (1)

Joel Priddy
Joel Priddy

Reputation: 441

This ought to do it (without making any changes to syntax preference or efficiency)

select c.MPolicyNumber, c.SNumber, c.IName, c.EDate, c.NewRenewal, c.GPremiumUSD,
       c.Status, b.deductibleinUSD, t.*
from IT.dbo.Blended c inner join
  (
    select a.[Policy_Number], a.[LOB], a.[Primary_R Amount]as Bound_deductibles,
           a.[Primary_R Type],  a.[Effective Date] as CAS_EDate
    from IT.dbo.BOUND_TAB a inner join 
         IT.dbo.RATER_OF_RECORD b on a.idxFile = b.[Bound Rater]
  ) t ON t.Policy_Number = c.MPolicyNumber
where c.deductibleinUSD > 0 and c.ProductLine in ('Health','Cas')
order by c.EDate

Upvotes: 2

Related Questions