Deeply Nested INNER JOINs

				
--**************************************
-- Name: Deeply Nested INNER JOINs
-- Description:To create a large working table from four disparate tables to demonstrate nesting INNER JOINS multiple times using multiple ON criteria and multiple WHERE criteria.
-- By: Tim Daniels
--
-- Returns:Single table with all data in it
--
-- Assumes:This is specific to a set of tables, however the concept is not shown well in any documentation and took some time to figure out the nuances of it.
--
--This code is copyrighted and has-- limited warranties.Please see http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=246&lngWId=5--for details.--**************************************

SELECT INSURED_RECORD.INSURED_ID,
EMPLOYMENT_HISTORY.EMP_HIST_SEQUENCE,
EMPLOYMENT_HISTORY.BANK_ID,
INSURED_PLAN.PLAN_CODE, 
INSURED_PLAN.PLAN_CODE_SEQ,
IR_FIRST_NAME,
IR_MIDDLE_INITIAL,
IR_LAST_NAME,
IR_CURRENT_AGE,
EH_DATE_HIRED, 
EH_LIFE_CLASS, 
EH_WEEKLY_SALARY,
EH_MONTHLY_SALARY, 
EH_ANNUAL_SALARY,
IP_COVG_CALC_CODE,
IP_PREMIUM_CALC_CODE_1,
IP_PREMIUM_CALC_CODE_2,
IPH_COVG_VOLUME, 
IPH_PREMIUM
FROMINSURED_RECORD INNER JOIN
 (EMPLOYMENT_HISTORY INNER JOIN
(INSURED_PLAN INNER JOIN 
 INSURED_PLAN_HISTORY
 ON (INSURED_PLAN.INSURED_ID = INSURED_PLAN_HISTORY.INSURED_ID)
AND (INSURED_PLAN.EMP_HIST_SEQUENCE =
INSURED_PLAN_HISTORY.EMP_HIST_SEQUENCE)
AND (INSURED_PLAN.BANK_ID = INSURED_PLAN_HISTORY.BANK_ID)
AND (INSURED_PLAN.PLAN_CODE = INSURED_PLAN_HISTORY.PLAN_CODE)
AND (INSURED_PLAN.PLAN_CODE_SEQ =
INSURED_PLAN_HISTORY.PLAN_CODE_SEQ))
 ON (EMPLOYMENT_HISTORY.INSURED_ID = INSURED_PLAN.INSURED_ID)
AND (EMPLOYMENT_HISTORY.EMP_HIST_SEQUENCE =
INSURED_PLAN.EMP_HIST_SEQUENCE)
AND (EMPLOYMENT_HISTORY.BANK_ID = INSURED_PLAN.BANK_ID))
ON (INSURED_RECORD.INSURED_ID = EMPLOYMENT_HISTORY.INSURED_ID)
WHERE (INSURED_PLAN.BANK_ID = 999999999)
 AND (EMPLOYMENT_HISTORY.BANK_ID = 999999999)
 AND (IPH_SUPERCEDED_DATE IS NULL)
 AND (IP_PLAN_STATUS <> 'T') 
 AND ((EH_STATUS = 'T' AND EH_DATE_TERMINATED > '10/15/2000')
OR (EH_STATUS <> 'T'))
ORDER BY INSURED_RECORD.INSURED_ID,
 INSURED_PLAN.PLAN_CODE,
 INSURED_PLAN.PLAN_CODE_SEQ