General information
Category:
Coding, syntax & commands
>>>>>>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.
Why?
Vlad
Previous
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only