>>>>>I need to Select information from 2 tables and put the results into one cursor. I think I need to do some kind of subquery but I can't quite come up with the syntax to make it work. Here is what I need:
>>>>>
>>>>>I have one table which contains, agreeno(agreement number),dist(district number), begdate(beginning date) and enddate. I need the agreeno and dist where begdate<=date()<=enddate.
>>>>>SELECT DISTINCT agreeno,dist FROM agreemnt WHERE begdate<=date() AND enddate>=date()
>>>>>
>>>>>Then I need all fields from header.dbf for each dist selected above.
>>>>>
>>>>>Any suggestions?
>>>>>
>>>>>Shane
>>>>
>>>>Suggestions? Just join header.dbf table to the same Select command (I guess you should have some fields linking two tables together).
>>>
>>>This is what I came up with, let me know please if there is problem that I am overlooking.
>>>
>>>SELECT * FROM header WHERE alltrim(dist) IN (SELECT alltrim(dist) FROM agreemnt WHERE begdate <=date() AND enddate>=date())
>>>
>>>I had to put the alltrim on dist because without it FoxPro thinks it's short for DISTINCT.
>>
>>Yes, it should work, but not so fast as normal joint:
>>Select Header.* ;
>>From Header, Agreemnt ;
>>Where Header.dist=Agreemnt.dist ;
>>And Agreemnt.begdate <=date() ;
>>And Agreemnt.enddate>=date()
>
>You don't respect the DISTINCT condition in this SELECT.
>
>Vlad

I know, but, you bet, it's not the case.