Hi!

You can solve this in VFP only partially, using 2 queries.


Finally, if you're interested, in SQL server you can write following:

select parent1.id_parent, 
    (select sum(child1.child1) from child1 where child1.id_parent = parent1.id_parent), 
    (select sum(child2.child2) from child2 where child2.id_parent = parent1.id_parent)
  from parent1

In VFP - you cannot.


>I thought I've seen this a few time here lately, but all the threads I could dig up didn't help.
>
>Run this little program:
>
>

>* Create the parent table with 5 IDs
>create table parent1 (id_parent i)
>for lnX = 1 to 5
>	append blank
>	replace id_parent with lnX
>endfor
>
>* Then the child table, with two records for each parent, and then a value
>create table child1 (id_parent i, child1 n)
>for lnX = 1 to 5
>	append blank
>	replace id_parent with lnX
>endfor
>for lnX = 1 to 5
>	append blank
>	replace id_parent with lnX
>endfor
>replace child1 with recno() all
>
>* Same thing here with different values
>create table child2 (id_parent i, child2 n)
>for lnX = 1 to 5
>	append blank
>	replace id_parent with lnX
>endfor
>for lnX = 1 to 5
>	append blank
>	replace id_parent with lnX
>endfor
>replace child2 with recno() + 10 all
>
>* Now select the ID, the total values for each ID in Child1 in one column
>* and total the values for each ID in Child2 in another
>select parent1.id_parent, sum(child1.child1), sum(child2.child2) ;
>	from parent1 left join child1 ;
>		on parent1.id_parent = child1.id_parent ;
>	left join child2 on parent1.id_parent = child2.id_parent ;
>	group by parent1.id_parent
>
>
>*MH	select parent1.id_parent, sum(child1.child1), sum(child2.child2) ;
>*MH		from child1 right join parent1 ;
>*MH			on parent1.id_parent = child1.id_parent ;
>*MH		left join child2 on parent1.id_parent = child2.id_parent ;
>*MH		group by parent1.id_parent

>Now, I see the second column of record 1 as 14. But, thats not what I want. If you select child1, and look at teh records were id_parent=1, you'll see it only adds up to 7, which is what I want. What I think is happening, is that since there are also 2 records in Child2 where id_parent=1, the value is doubled by two. Its doing a full outer join, or something.
>
>In fact, if you add a third record to Child2 with id_parent=1, the result of the SELECT is not 3 * 7 instead of 2 * 7. Also, removing the join to Child2 gives the correct result.
>
>How do I fix this to give me what I want?