GREAT!
that one works exactly like i need it to! i did, however, had to take the "," out in "left join inv b on a.pn = b.pn, ;".
how strange that that works where my last version did not. But i "don't look a gift horse in the mouth" <vbg>
Thanks for your help!
>>>To avoid the .NULL. values, use the nvl() function, thusly:
>>>
>>>
>>>select ..., nvl(b.desc, space(30)) as desc, ...
>>>
>>>
>>>(assuming the description field has a width of 30 bytes).
>>
>>
>>nope, that still gives me the names of all vendors, even though they do not belong to that part.
>
>
>>sele ;
>> a.pn, ;
>> nvl(b.desc, space(35)) as desc, ;
>> nvl(c.name, space(35)) as name ;
>> distinct ;
>> from ;
>> pnvendor a left join inv b on a.pn = b.pn, ;
>> pnvendor a left join vendor c on a.vendorid = c.vendorid ;
>> where ;
>> "1004" $ a.vendorid .and. ;
>> "04AV205" $ a.pn
>>
>
>I am not sure why it works at all. I assume you want to join a with b, and a with c. I would rewrite this as follows:
>
>
>sele ;
> a.pn, ;
> nvl(b.desc, space(35)) as desc, ;
> nvl(c.name, space(35)) as name ;
> distinct ;
> from ;
> pnvendor a;
> left join inv b on a.pn = b.pn, ;
> left join vendor c on a.vendorid = c.vendorid ;
> where ;
> "1004" $ a.vendorid .and. ;
> "04AV205" $ a.pn
>
>
>This is not only aesthetic: I omitted the second mention of "pnvendor a". I am not sure whether both versions would produce the same results.
>
>Please note that the LEFT clause will include all data on the left side of the relation, even if there is no equivalent on the right side. This would include all vendors, in your case, even if they have no data on the right side.
>
>If you don't want this, omit the LEFT clause (use only JOIN) on one relation, or both.
>
>HTH,
>
>Hilmar.