Tamar,

Here is the code....in a nutshell, I have many places in an application where I have multiple 1-many relationships, and want to bring back data in one shot. I'm not positive of what the GROUP BY would need to be, so I'm guessing it would need to be all three columns.

If you need any more info, let me know.


Thanks for looking at this...
Kevin




* test to return scalar value from a subquery as a column
* Code runs in SQL Server (making tweaks for syntax differences), but generates
* 'invalid use of a subquery' in VFP 8

* Can't use straight join, as some jobs may have just hours and no materials, some the reverse, and some have both
* Can't use outer join, as it produces double-counting

* Only solution in VFP is to do multiple queries, or one query with a UDF

* this would be really handy to have in VFP9, as I have many instances across apps where I have multiple
* one-to-many situations where I'd love to do the query in one shot




CLOSE DATABASES ALL
CREATE CURSOR JobMast (JobNumber I(4))

CREATE CURSOR JobHours (JobNumber I(4), Hours N(14,2))
CREATE CURSOR JobMaterials (JobNumber I(4), Amount N(14,2))

INSERT INTO JobMast (JobNumber) VALUES (1)
INSERT INTO JobMast (JobNumber) VALUES (2)

INSERT INTO JobHours (JobNumber, Hours) VALUES (1,8)
INSERT INTO JobHours (JobNumber, Hours) VALUES (1,7)
INSERT INTO JobHours (JobNumber, Hours) VALUES (1,6)

INSERT INTO JobHours (JobNumber, Hours) VALUES (2,3)
INSERT INTO JobHours (JobNumber, Hours) VALUES (2,4)
INSERT INTO JobHours (JobNumber, Hours) VALUES (2,5)


INSERT INTO JobMaterials (JobNumber, Amount) VALUES (1,100)
INSERT INTO JobMaterials (JobNumber, Amount) VALUES (1,200)
INSERT INTO JobMaterials (JobNumber, Amount) VALUES (1,300)
INSERT INTO JobMaterials (JobNumber, Amount) VALUES (1,400)
INSERT INTO JobMaterials (JobNumber, Amount) VALUES (1,500)

SELECT JobMast.JobNumber, ;
(SELECT sum(JobHours.Hours) FROM JobHours WHERE JobHours.JobNumber = JobMast.JobNumber) AS JobHours, ;
(SELECT SUM(JobMaterials.Amount) from JobMaterials where JobMaterials.Jobnumber = Jobmast.Jobnumber) as JobAmount ;
from Jobmast group by JobMast.JobNumber, JobHours,JobAmount



** should get the following:

* JobNumber Hours Amount
* 1 21 1500.00
* 2 12 0 (or NULL)