Simplified scenario (question at the bottom):
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:
- A few columns from
Customer
- Insurance 1 - columns from InsuranceCompany and InsurancePlan tables where InsuranceCoverage.line = 1
- Insurance 2 - columns from InsuranceCompany and InsurancePlan tables where InsuranceCoverage.line = 2
- Insurance 3 - columns from InsuranceCompany and InsurancePlan tables where InsuranceCoverage.line = 3
In SQL, I can do it this way:
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
QUESTION: How do I join the same field multiple times with different criteria in Crystal?
IP Logged
