Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Using Select SQL in more than 1 Table
Message
From
09/04/2001 23:04:09
 
 
To
All
General information
Forum:
Visual FoxPro
Category:
Other
Title:
Using Select SQL in more than 1 Table
Miscellaneous
Thread ID:
00493880
Message ID:
00493880
Views:
69
I have 2 tables, CLIENT and ADDRESS, both with a unique MNO field which is the client ID number. The ADDRESS table can have any number of records for each client, with a field ADS as the date of address change.

Using Select SQL, I need to extract a list of client names from the CLIENT table and their most recent address record from the ADDRESS table.

I have tried using the MAX(ADS) to extract the most recent record from the ADDRESS table, but it only extracts the actual most recent ADS field itself....the actual address data appears to come from any record in the ADDRESS table.

The code used is:

select max(address.ads), address.ad1, trim(client.sn)+', '+trim(client.fn) as surname , client.mno ;
from tcdata!address ;
inner join client ;
on address.mno=client.mno ;
where address.can=0 ;
group by address.mno ;
order by surname ;
into cursor boe

Any suggestions would be gratefully received

Regards

Bob
Next
Reply
Map
View

Click here to load this message in the networking platform