Home » SQL & PL/SQL » SQL & PL/SQL » PROBLEM WITH LEFT OUTER JOIN
PROBLEM WITH LEFT OUTER JOIN [message #223570] Fri, 09 March 2007 10:45 Go to next message
ccrespowmg
Messages: 1
Registered: March 2007
Junior Member
have two queries, but when I do a left outer join and put a condition, then it excludes the records I need. Heres the SQL Statements:
**This is the sql which contains all the data I need.
SELECT 
    "ORDERS"."CODE", "ORDERS"."STATUS", "ORDERS"."ORDERDATE", "USRINFO"."LASTNAME", "PERSON"."SEARCHNAME", "PERSON"."DATEOFBIRTH"
FROM
    "ML"."ORDERS" "ORDERS",
    "ML"."USRINFO" "USRINFO",
    "ML"."PERSON" "PERSON"
WHERE
    "ORDERS"."USRID" = "USRINFO"."PVID" AND
    "ORDERS"."PID" = "PERSON"."PID"(+) AND
    "ORDERS"."STATUS" <> 'X' AND
    "ORDERS"."CODE" LIKE '125F%' AND
    "ORDERS"."ORDERDATE" > TO_DATE ('01-01-2007', 'DD-MM-YYYY')
ORDER BY
    "USRINFO"."LASTNAME" ASC,
    "PERSON"."SEARCHNAME" ASC


Then I needed another two tables for the left outer join of PID
SELECT 
    "ORDERS"."CODE", "ORDERS"."STATUS", "ORDERS"."ORDERDATE", "USRINFO"."LASTNAME", "PERSON"."SEARCHNAME", "PERSON"."DATEOFBIRTH", "LASTLABS"."HDID", "LASTLABS"."MAXLABDATE", "RPTOBS"."OBSVALUE"
FROM
    "ML"."ORDERS" "ORDERS",
    "ML"."USRINFO" "USRINFO",
    "ML"."PERSON" "PERSON",
    "ML"."LASTLABS" "LASTLABS",
    "ML"."RPTOBS" "RPTOBS"
WHERE
    "ORDERS"."USRID" = "USRINFO"."PVID" AND
    "ORDERS"."PID" = "PERSON"."PID"(+) AND
    "PERSON"."PID" = "LASTLABS"."PID"(+) AND
    "LASTLABS"."HDID" = "RPTOBS"."HDID" AND
    "LASTLABS"."MAXLABDATE" = "RPTOBS"."OBSDATE" AND
    "ORDERS"."STATUS" <> 'X' AND
    "ORDERS"."CODE" LIKE '125F%' AND
    "ORDERS"."ORDERDATE" > TO_DATE ('01-01-2007', 'DD-MM-YYYY') AND
    "LASTLABS"."MAXLABDATE" > TO_DATE ('01-01-2007', 'DD-MM-YYYY') AND
    "LASTLABS"."HDID" = 44917. 
ORDER BY
    "USRINFO"."LASTNAME" ASC,
    "PERSON"."SEARCHNAME" ASC

But the second query exludes data from the first one that don't have "LASTLABS"."HDID".
Any reason why this is happening?
Re: PROBLEM WITH LEFT OUTER JOIN [message #223646 is a reply to message #223570] Fri, 09 March 2007 23:57 Go to previous message
bonker
Messages: 402
Registered: July 2005
Senior Member
that is because this join condition

PERSON"."PID" = "LASTLABS"."PID"(+)

says that make up rows in LASTLABS table with all
columns as null for those Persons.PID that are not
available in Lastlabs.

After this you have these conditions


"LASTLABS"."HDID" = "RPTOBS"."HDID" AND
"LASTLABS"."MAXLABDATE" = "RPTOBS"."OBSDATE"



which effectively makes the query to exclude the results that were included due to the earlier outer join. This because null is neither equal or not equal to anything and therefore I guess you are not getting data that you are expecting.

In case RPTOBS.HDID and RPTOBS.OBSDATE do not have any null values in them then you can possibly try this and check if you get the expected results

coalesce("LASTLABS"."HDID","RPTOBS"."HDID") = "RPTOBS"."HDID" AND
coalesce("LASTLABS"."MAXLABDATE","RPTOBS"."OBSDATE" 
) = "RPTOBS"."OBSDATE" 




Previous Topic: PL/SQL - Getting counts after Merge Statement completion.
Next Topic: VIEW
Goto Forum:
  


Current Time: Sun Dec 11 02:40:19 CST 2016

Total time taken to generate the page: 0.12677 seconds