Hi Wayne,

The first JOIN between parent and child tables creates intermidiate set with two records. In the second JOIN each record of the intermidiate set is joined with each record of child2 so result set includes 6 records. If you change data as follow you'll see that each child1 record is repeated 3 times and each child2 record is repeated 2 times.

CREATE CURSOR __child1 (ifk i, namount1 n(10))
INSERT INTO __child1 VALUES( 001, 1100)
INSERT INTO __child1 VALUES( 001, 1200)

CREATE CURSOR __child2 (ifk i, namount2 n(10))
INSERT INTO __child2 VALUES( 001, 2100)
INSERT INTO __child2 VALUES( 001, 2200)
INSERT INTO __child2 VALUES( 001, 2300)

SELECT * ;
	FROM __parent;
	left OUTER JOIN __child1 ON __parent.ipkey = __child1.ifk;
	left OUTER JOIN __child2 ON __parent.ipkey = __child2.ifk 

To get correct result you can sum data in each of child tables into cursor and then join results with parent table

SELECT ifk, sum(__child1.namount1) as __c1 ;
	FROM __child1 ;
	GROUP BY 1 ;
	INTO CURSOR crsSum1

SELECT ifk, sum(__child2.namount2) as __c2 ;
	FROM __child2 ;
	GROUP BY 1 ;
	INTO CURSOR crsSum2

SELECT __parent.ipkey, crsSum1.__c1, crsSum2.__c2 ;
	FROM __parent;
	left OUTER JOIN crsSum1 ON __parent.ipkey = crsSum1.ifk ;
	left OUTER JOIN crsSum2 ON __parent.ipkey = crsSum2.ifk 

In Sql Server it can be done with one select using derived tables.

SELECT __parent.ipkey, cs1.__c1, cs2.__c2 ;
	FROM __parent;
	left OUTER JOIN (SELECT ifk, sum(__child1.namount1) as __c1 ;
				FROM __child1 GROUP BY ifk ) cs1 ON __parent.ipkey = cs1.ifk ;
	left OUTER JOIN (SELECT ifk, sum(__child2.namount2) as __c2 ;
				FROM __child2 GROUP BY ifk ) cs2 ON __parent.ipkey = cs2.ifk 

>Who can explain this/help us get the right results? We are trying to get the sums of the values from the two children - but the return set brings back more than expected. If you take off the sums and group by's you can see what is being returned.
>Same in vfp6,7,8 and SQL Server
>
>
<snip>