>>>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.

First of all: never use ALLTRIM() in a SELECT-SQL.

On the steps that is done internally by SELECT-SQL before starting to produce the results is to determine structure of the result. Ie: the type and the length of each field. This is done by evaluating each expression from your SELECT on the first record in the table. So, in your case, if the length of alltrim(dist) is 5 for the first record, all records in the result will have 5 characters for this field.

More "generic": never use an expression that gives variable length results in a SELECT-SQL. I already said why is this bad in a field expression. It's also bad in a WHERE condition: you never know when and how FoxPro will use an index to optimize the WHERE condition. And there are no variable length key indexes in FoxPro. Useless to say that the results may be at least weird in this case! :)

In your case, there's no need for alltrim(). You must only use the alias in front of the field:

SELECT * FROM header WHERE header.dist IN (SELECT agreemnt.dist FROM agreemnt WHERE agreemnt.begdate <=date() AND agreemnt.enddate>=date())

BTW, it's a very good practice to always use the alias in front of any field. The most "weird" bugs I found are caused by the use of the field names without alias qualification.

Vlad