CODEWITHSUNDEEP
sql-servert-sqlsubquery
texas_mike81
texas_mike81

Reputation: 71

T-SQL: Same fields using multiple joins

I reviewed similar questions but could not find an answer to my specific quandry. I'm working with SQL Server 2008 (T-SQL in SQL Server Management Studio) (but much more used to Oracle and Crystal Reports).

Simplified scenario:

Table Customer

customerID (pk)...

Table InsuranceCoverage

customerID (composite pk)
line (composite pk)
insCompanyID (fk)
insPlanID (fk)

Table InsuranceCompany

insCompanyID
insCompanyName
insCompanyAddr

Table InsurancePlan

insPlanID
insPlanName
insPlanClass

I need a report that basically returns the following on one row:

  1. A few columns from Customer
  2. Insurance 1 - columns from InsuranceCompany and InsurancePlan tables where InsuranceCoverage.line = 1
  3. Insurance 2 - columns from InsuranceCompany and InsurancePlan tables where InsuranceCoverage.line = 2
  4. Insurance 3 - columns from InsuranceCompany and InsurancePlan tables where InsuranceCoverage.line = 3

I feel very stupid not being able to figure this out. One customer might have up to three insurances. This would be easy to write multiple queries for, but I've got to get it set up so it can run automatically 1x/month. I've used the same table multiple times in the same report by using aliases on the joins before, but that won't work here because of the InsuranceCoverage.line criteria, right? Is a subquery in the from clause the answer?

Upvotes: 1

Views: 1141

Answers (3)

Rodolfo
Rodolfo

Reputation: 4183

you can do a union of 2, 3, 4 as a derived table and join 1 with that, like...

select t1.a,t1.b,t1.c, t2.d, t2.e, t2.f from customer t1, 
(select fk, d as d, 0 as e, 0 as f from ic where line=1
  union select fk, 0, e, 0 from ic where line=2
  union select fk, 0, 0, f from ic where line=3) as t2
where t1.pk = t2. fk

something like that

edit: oh, right, if they can have no insurance then change it to a left join like ...

select t1.a,t1.b,t1.c, t2.d, t2.e, t2.f from customer t1 left join  
(select fk, d as d, 0 as e, 0 as f from ic where line=1
  union select fk, 0, e, 0 from ic where line=2
  union select fk, 0, 0, f from ic where line=3) as t2
 on t1.pk = t2.fk

... or something like that :)

Upvotes: 1

Jeremy Pridemore
Jeremy Pridemore

Reputation: 1995

I set up some table variables to show that this query works. You'll need to replace them with the real table and column names. I believe something like this would work for you:

DECLARE @Customer TABLE (CustomerId INT)

DECLARE @InsuranceCoverage TABLE
(
    CustomerId INT
    , Line INT
    , InsuranceCompanyId INT
    , InsurancePlanId INT
)

DECLARE @InsuranceCompany TABLE
(
    Id INT
    , Name VARCHAR(100)
    , Addr VARCHAR(100)
)

DECLARE @InsurancePlan TABLE
(
    Id INT
    , Name VARCHAR(100)
    , Class VARCHAR(100)
)

SELECT
    C.* -- Customer colums.
    -- Insurance1 columns.
    , ICmp1.*
    , IP1.*
    -- Insurance2 columns.
    , ICmp2.*
    , IP2.*
    -- Insurance3 columns.
    , ICmp3.*
    , IP3.*
    FROM
    @Customer C
    LEFT JOIN @InsuranceCoverage ICov1          
        INNER JOIN @InsuranceCompany ICmp1
            ON ICmp1.Id = ICov1.InsuranceCompanyId
        INNER JOIN @InsurancePlan IP1
            ON IP1.Id = ICov1.InsurancePlanId
        ON ICov1.CustomerId = C.CustomerId
        AND ICov1.Line = 1
    LEFT JOIN @InsuranceCoverage ICov2
        INNER JOIN @InsuranceCompany ICmp2
            ON ICmp2.Id = ICov2.InsuranceCompanyId
        INNER JOIN @InsurancePlan IP2
            ON IP2.Id = ICov2.InsurancePlanId
        ON ICov2.CustomerId = C.CustomerId
        AND ICov2.Line = 2
    LEFT JOIN @InsuranceCoverage ICov3
        INNER JOIN @InsuranceCompany ICmp3
            ON ICmp3.Id = ICov3.InsuranceCompanyId
        INNER JOIN @InsurancePlan IP3
            ON IP3.Id = ICov3.InsurancePlanId
        ON ICov3.CustomerId = C.CustomerId
        AND ICov3.Line = 3

Upvotes: 2

Chris Shain
Chris Shain

Reputation: 51319

Something like this?

SELECT
    c.CustomerID,
    cov1.*,
    cov2.*,
    cov3.*,
    insco1.insCompanyName as insCompanyName1,
    insco2.insCompanyName as insCompanyName2,
    insco3.insCompanyName as insCompanyName3,
    etc...
FROM
    Customer c
    LEFT OUTER JOIN InsuranceCoverage cov1 on cov1.CustomerID = c.CustomerID AND cov1.line = 1
    LEFT OUTER JOIN InsuranceCoverage cov2 on cov2.CustomerID = c.CustomerID AND cov2.line = 2
    LEFT OUTER JOIN InsuranceCoverage cov3 on cov3.CustomerID = c.CustomerID AND cov3.line = 3
    JOIN InsuranceCompany insco1 on insco1.insCompanyID = cov1.insCompanyID
    JOIN InsuranceCompany insco2 on insco2.insCompanyID = cov2.insCompanyID
    JOIN InsuranceCompany insco3 on insco3.insCompanyID = cov3.insCompanyID
    JOIN InsurancePlan inspl1 on inspl1.insPlanID = cov1.insPlanID
    JOIN InsurancePlan inspl2 on inspl2.insPlanID = cov2.insPlanID
    JOIN InsurancePlan inspl3 on inspl3.insPlanID = cov3.insPlanID

Upvotes: 2

Related Questions