Very Complex LEFT OUTER JOIN - Help!

[masterkeedu]

I am currently developing some webservices for a large insurance company.

We use oracle databases.

I need to pull data from two tables. liability,physdam

each table has duplicate records that use 2 more tables(physrelease,liabrelease) to detemine which I need. (basically different agents have different versions, so it supports legacy systems)

I need to pull this data as one record though, an outer join is ideal excpet for the foourth table.

So my first query works, but does not properly filter the results of table 3.

SELECT *
[b]from  EBR.VINMASTER_PHYSDAM_RELEASE PHYSREL, EBR.VINMASTER_PHYSDAM PHYS[/b]
LEFT OUTER JOIN EBR.VINMASTER_LIABILITY LIAB
ON PHYS.VEHICLE_IDENTIFICATION_NUMBER=LIAB.VEHICLE_IDENTIFICATION_NUMBER
WHERE  LIAB.EFFECTIVE_DATE <= TO_DATE('2007-06-28', 'YYYY-MM-DD') 
AND LIAB.vehicle_identification_number = '1VWCH016&C' 
AND [b]PHYS.VINMASTER_RELEASE_ID = PHYSREL.ID [/b]
AND PHYSREL.ID <= '0704' 
AND PHYSREL.RELEASE_DATE <= TO_DATE('2007-06-28', 'YYYY-MM-DD') 
AND PHYS.EFFECTIVE_DATE <= TO_DATE('2007-06-28', 'YYYY-MM-DD') 
AND PHYS.vehicle_identification_number = '1VWCH016&C'

the second bold line filtyers the results for me based on the first bold line, I need to do the same for the table listed after the join.

Unfortuantely I can not include the 4th table there like i can in a normal select query.

what i wanted;

SELECT *
from  EBR.VINMASTER_PHYSDAM_RELEASE PHYSREL, EBR.VINMASTER_PHYSDAM PHYS
[b]LEFT OUTER JOIN EBR.VINMASTER_LIABILITY LIAB, EBR.VINMASTER_LIABILITY_RELEASE LIABREL[/b]
ON PHYS.VEHICLE_IDENTIFICATION_NUMBER=LIAB.VEHICLE_IDENTIFICATION_NUMBER
WHERE LIABREL.ID <= '0703' 
AND LIABREL.RELEASE_DATE <= TO_DATE('2007-06-28', 'YYYY-MM-DD') 
AND LIAB.EFFECTIVE_DATE <= TO_DATE('2007-06-28', 'YYYY-MM-DD') 
AND LIAB.vehicle_identification_number = '1VWCH016&C' 
AND PHYS.VINMASTER_RELEASE_ID = PHYSREL.ID 
AND PHYSREL.ID <= '0704' 
AND PHYSREL.RELEASE_DATE <= TO_DATE('2007-06-28', 'YYYY-MM-DD') 
AND PHYS.EFFECTIVE_DATE <= TO_DATE('2007-06-28', 'YYYY-MM-DD') 
AND PHYS.vehicle_identification_number = '1VWCH016&C'

this however returns a 'missing keyword' error.

how do I include, and specify the 4th table 'LIABREL' without generating this issue.

shots in the dark are fine, I'm stumped.


Thank you!

 

[masterkeedu]

Solution

well after working long and hard i came up with what i though would suffice, then my project manager said, nope..

ya see it has to be a join, that can specify unique criteria for each table, which at first I thought was simple impossible, turns out its not.

like a bolt of lightning the solution hit me, I include here in case anyone else needs a complex outer join with unique criteria for each table. In my case the unique part is the release version our clients are on. we allow for maximum backwards compatibility, allowing clients to update their end when they can, not when we do.

/*******************************************************************************\
 | This query will pull physdam results, and if available, any liabilty results   |
 | if no liabilty is found, or dont match release, than those fields will be null |
 \*******************************************************************************/
 SELECT *  from  EBR.VINMASTER_physdam_RELEASE REL, EBR.VINMASTER_physdam VIN 
 LEFT OUTER JOIN EBR.VINMASTER_LIABILITY LIAB
 ON vin.VEHICLE_IDENTIFICATION_NUMBER=LIAB.VEHICLE_IDENTIFICATION_NUMBER
 and vin.iso_identification_number=liab.iso_identification_number
 and vin.manufacturer=liab.manufacturer
 and liab.release_id <= '0702'
 and vin.vinmaster_release_id <= '0704'
 and liab.start_date <= TO_DATE('2007-06-21', 'YYYY-MM-DD')
 and liab.end_date >= TO_DATE('2007-06-21', 'YYYY-MM-DD')
 WHERE 
 VIN.VINMASTER_RELEASE_ID = REL.ID 
 AND VIN.vehicle_identification_number = '1G2NE12T&W' 
 and vin.effective_date <= TO_DATE('2006-09-01', 'YYYY-MM-DD')
 and vin.end_date >= TO_DATE('2007-06-21', 'YYYY-MM-DD')
 and vin.start_date<= TO_DATE('2007-06-21', 'YYYY-MM-DD')
 ORDER BY vin.FULL_MODEL_NAME, vin.Effective_date desc,vin.vinmaster_release_id desc

 

[Ian]

Thanks for posting the solution This sort of thing is way beyond me, but I know it will help someone else

 

[masterkeedu]

Hey its my pleasure,

This board, and some other more specific boards, has been an invaluable resource for me in the past.

I'm glad to help make it a lilttle richer for other users.