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.

Please advise.




Regards
Bhavbhuti
___________________________________________
Softwares for Indian Businesses at:
http://venussoftop.tripod.com
namitbn@...
___________________________________________

----------


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.735 / Virus Database: 489 - Release Date: 06/08/2004


[Non-text portions of this message have been removed]