As an addition: now that it works, i tried it without the distingt clause, and it works correctly (3 records in the result) without that clause.

Thanks again for everybody helping me out on this one!





>>>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.