At 12:29 PM 9/08/2004 +0530, you wrote:

>Hi all
>
>I have just started an app in VFP6 for a FireBird 1.5.1.4481 database
>containing one table mPlants which is self-joined, for a hierarchy tree
>data structure. I have to use the following SQL in VFP view to get it to
>work, though I got the timgs done but it is the wrong way round. I have
>tried modifying the SQL by using INNER OUTER LEFT RIGHT, but this is the
>only way the data displays right. The {IJ stands for INNER JOIN in VFP (it
>is new for me but it works) and I have tried OJ as well as specifically
>mentioning OUTER clause in the SQL.
>
>SELECT MPLANTS_B.*, ;
> MPLANTS_A.CNAME AS CGROUPNAME ;
> FROM {IJ MPLANTS MPLANTS_A ;
> RIGHT JOIN MPLANTS MPLANTS_B ;
> ON MPLANTS_B.IPID = MPLANTS_A.IID} ;
> ORDER BY MPLANTS_B.CCODE, MPLANTS_B.CNAME
>
>If I am not mistaken I had previously successfully used, on a test self
>join database, a few months back in 1.5.0 a view SQL as below
>
>SELECT MPLANTS.*, ;
> MPLANTS_A.CNAME AS CGROUPNAME ;
> FROM MPLANTS ;
> LEFT JOIN MPLANTS MPLANTS_A ;
> ON MPLANTS_A.IPID = MPLANTS.IID ;
> ORDER BY MPLANTS.CCODE, MPLANTS.CNAME
>
>This SQL give a NULL return for all the records this time round.

The semicolons scattered around your query are illegal and you need full
aliasing for *each* usage of the table, i.e. you can't use the plain table
identifier for one instance and an alias for the other.

Assuming IPID is the primary key and IID is the foreign key referencing the
parent, the following query will get one row for each primary row in
MPLANTS, with the last output column being the name of its parent, if it
has one. For rows that have no parents, there will be nulls in that column:

SELECT
child.*,
parent.CNAME AS CGROUPNAME
FROM MPLANTS child
LEFT JOIN MPLANTS parent
ON child.IID = parent.IPID
ORDER BY child.CCODE, child.CNAME

/heLen