>Please disregard my last reply, because:
>sele ;
> a.pn, ;
> b.desc, ;
> c.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
>
>gives me totaly wrong results. I probably didn't discribe the problem correctly.
>
>i need to join the 3 tables (pnvendor beeing the parent table). however it is possible that in 1 (or both) of the child tables the approbriate record is missing. if i were to 'join' the tbls through the where clause i would not get all the required records from pnvendor.
That is what the LEFT is for: get all records from the left side of the relation, even if there is no equivalent on the right. Fields from the missing records will usually be filled with .NULL. values.
>the querry i need should include all relevant records from the pnvendor and leave the fields (b.desc, c.name) blank if there is no matching record in the child table.
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).
Difference in opinions hath cost many millions of lives: for instance, whether flesh be bread, or bread be flesh; whether whistling be a vice or a virtue; whether it be better to kiss a post, or throw it into the fire... (from Gulliver's Travels)