Sub-Query in the FROM clause of a Query?

[FoxAmateur]

Does any know if VFP can handle a Sub-Query in the FROM clause of a Query?

I get a systax error for the following query.

SELECT county, zip, count(*) ;
FROM (SELECT pm.*, z.county ;
FROM pm_return pm, zipcodes z ;
WHERE pm.zip == z.zip AND match = 'Y') ;
GROUP BY county, zip ;
INTO TABLE pmcounts

I can get my results if I do it in 2 separate queries:

SELECT pm.*, z.county ;
FROM pm_return pm, zipcodes z ;
WHERE pm.zip == z.zip AND match = 'Y' ;
INTO TABLE pm_return2


SELECT county, zip, count(*) ;
FROM pm_return ;
GROUP BY county, zip ;
INTO TABLE pmcounts

-Chris

 

[mkrausnick]

Yeah, you'd need two queries to do that.

Mike Krausnick

 

[FoxAmateur]

Thanks Mike!

I found a way to make it one query:

select z.county, pm.zip, count(*) ;
from zipcodes z ;
inner join pm_return pm on pm.zip == z.zip and pm.match = 'Y' ;
GROUP BY z.county, pm.zip ;
INTO TABLE pmcounts

-Chris