Hi!
Try:
SELECT MSALESREPS_A.*,
MSALESREPS_B.CNAME AS CSPONNAME,
MSALESREPS_B.CCITY AS CSPONCITY,
MSALESREPS_B.CCODE AS CSPONCODE
FROM MSALESREPS MSALESREPS_A
LEFT OUTER JOIN MSALESREPS MSALESREPS_B
ON MSALESREPS_A.IPID = MSALESREPS_B.IID
ORDER BY MSALESREPS_A.CNAME, MSALESREPS_B.CCITY


--- In firebird-support@yahoogroups.com, Venus Software Operations
<venussoftop@m...> wrote:

> Hi All
>
> Recently I have this join:
>
>
>
> SELECT MSALESREPS.*,
> MSALESREPS_A.CNAME AS CSPONNAME,
> MSALESREPS_A.CCITY AS CSPONCITY,
> MSALESREPS_A.CCODE AS CSPONCODE
> FROM MSALESREPS MSALESREPS
> RIGHT OUTER JOIN MSALESREPS MSALESREPS_A
> ON MSALESREPS.IPID = MSALESREPS_A.IID
> ORDER BY MSALESREPS.CNAME, MSALESREPS.CCITY
>
>
>
> This is a self joined table, but not necessarily all the records

are self

> joined. the above works only to an extent, in the case of a record

which is

> not self joined (iPID is not populated) still a join is performed

with the

> first record giving the 3 cSpon* fields with wrong values when

they are

> supposed to be blank/null. This not-self-joined record is to be

available

> in the cursor/query as also the self-joined records with their

respective

> values in the cSpon* fields.
>
> In VFP a LEFT JOIN would solve such a problem and I have tried it
> successfully. In FB LEFT JOIN of the original VFP code does not

work at all

> and returns a null record.
>
> Please advise.
>
> Regards
> Bhavbhuti
>
>
> ----------
>
>
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.560 / Virus Database: 352 - Release Date: 08/01/2004
>
>
> [Non-text portions of this message have been removed]