>>>Hi everyone.
>>>
>>>Can anyone tell me why the following RIGHT OUTER JOIN statement returns the same records as an INNER JOIN?
>>>
>>>SELECT v.custid, l.custid, v.upsino, l.upsino, v.datinp, l.puldat, v.optemp, v.pktemp, l.ift, l.color;
>>>FROM visual v ;
>>>RIGHT OUTER JOIN liquid l ;
>>>ON v.custid + v.upsino + DTOS(v.datinp) = l.custid + l.upsino + DTOS(l.puldat) ;
>>>WHERE v.custid = "AL100239" AND v.upsino = "0000001";
>>>ORDER BY v.upsino, v.datinp
>>>
>>>I have records in the liquid table that have a UPSINO = "0000001" with a puldat that has NO MATCHING datinp record in the VISUAL table. Why do these records not show up in the results with the non matching fields set to NULL.
>>>
>>>I am trying extract all records from the LIQUID table that match my lookup criteria whether on not they have a matching record in VISUAL.
>>>
>>>
>>>Thank you.
>>
>>NULL values are filtering out your WHERE statament. Try replacing aliases (i.e. LEFT instead of RIGHT JOIN), and Check for condition in "Liquid" instead of "visual".
>>Please find code below:
>>
>>

>>SELECT v.custid, l.custid, v.upsino, l.upsino, v.datinp, l.puldat, v.optemp, v.pktemp, l.ift, l.color;
>>FROM liquid l ;
>>LEFT OUTER JOIN visual v ;
>>ON v.custid + v.upsino + DTOS(v.datinp) = l.custid + l.upsino + DTOS(l.puldat) ;
>>WHERE l.custid = "AL100239" AND l.upsino = "0000001";
>>ORDER BY v.upsino, v.datinp
>>

>
>Zoran,
>
>Thank you. Your suggestion to check for condition in the "Liquid" instead of "Visual" works for me. However, I did not replace the aliases as you suggested, I actually have to join multiple tables to the Visual as shown below. I have to JOIN four more tables to the Visual. Am I correct in assuming that I can simply add each table with a RIGHT OUTER JOIN and add an OR condition to include that table?
>
>Here's what I'm trying now:
>
>

>
>SELECT v.custid, l.custid, v.upsino, l.upsino, v.datinp, l.puldat, v.optemp, v.pktemp, l.ift, d.hydrogen, i.aluminum ;
>FROM visual v ;
>RIGHT OUTER JOIN liquid l ON v.custid + v.upsino + DTOS(v.datinp) = l.custid + l.upsino + DTOS(l.puldat) ;
>RIGHT OUTER JOIN dga d ON v.custid + v.upsino + DTOS(v.datinp) = d.custid + d.upsino + DTOS(d.puldat) ;
>RIGHT OUTER JOIN icp i ON v.custid + v.upsino + DTOS(v.datinp) = i.custid + i.upsino + DTOS(i.puldat) ;
>WHERE l.custid = "AL100239" AND l.upsino = "0000001" ;
>OR d.custid = "AL100239" AND d.upsino = "0000001" ;
>OR i.custid = "AL100239" AND i.upsino = "0000001" ;
>ORDER BY v.upsino
>
>

>Thanks again


Elgin,

That is fine. The only problem with OUTER JOINs is that you have to handle NULL values in WHERE clause. In example above you should probably add something like:

OR NVL(d.custid,"AL100239") = "AL100239" AND NVL(d.upsino,"0000001") = "0000001"

in case you want to show records that do not have the matching "dga" records.

Also, I would suggest use of IS NULL / IS NOT NULL clause.
for example:

d.custid IS NULL OR d.custid = "AL100239"

Cheers,

Zoran