DBF Query Join? 1

[MichMat]

Hi All,

First post and absolute begginer.

I have 2 dbf tables. One has todays drivers and the other has drivers history(ie each past driving job) I want to create a dataset? which has the driving history for each driver engaged today so that I can do some stuff with it.

The drivers(today) table has the drivers name and some other details for over all performance
The drivers (history) has hundreds of drivers each with a name and the details of that particular job. So any one driver could have as many as 500 jobs (records/lines).

The logic (there is a funny one) is this :

Select all (fields/entire row of record) from driver(history) that corespond to all names from Driver(today)

I can access the databases ok and up to now I had a loop where I create a dataset for each drivers history as I move through the todays roster... Very slow Using the folowing sql where the CurrentDriver is a string value of each and all drivers in todays table

"Select * from DHistory.DBF WHERE [DRIVER] = "& "'" & CurrentDriver & "'"

Im trying to write it so that all the drivers historys are returned at once so that I can work with them further.

I have so far this and it dosent work (obviously)

"Select * FROM DHistory.DBF,DToday.DBF where DHistory.DRIVER = DToday.DRIVER"

If someone could tell me what I need to do to 'join' these tables together and if infact this can be done using the resources I outlined above.

Thank you so much

Michal

 

[r937]

it doesn't work? why would that be obvious? it looks okay to me -- well, except that it doesn't use JOIN syntax

but wait...

what database is this? clipper? foxpro? xbase? dbase?

you have posted the question in the ANSI SQL forum, and you are likely going to get a better answer in the forum for wahtever database that is

r937.com | rudy.ca

 

[MichMat]

Thanks,

dbase is the data bases, I wouldnt think that that it would matter in the sql statment. The join statment ? In the examples in the tutorial there was no mention of such thing.

Michal

 

[r937]

Select *
FROM DHistory.DBF
INNER
JOIN DToday.DBF
ON DToday.DRIVER = DHistory.DRIVER

r937.com | rudy.ca

 

[MichMat]

Thanks R937

While your code did not work, due to syntax error It at least showed me that I was heading in the right direction. Most times that is as good as any specific advice.

Through trial and error I came to a working solution,
I WAS SO CLOSE BEFORE

"Select * FROM DHistory,DToday where DHistory.DRIVER = DToday.DRIVER"

TAKE OUT THE .DBF from the table names and it goes through no problems taking about 10 seconds to collect 15000 records. Where previously it was taking 3-6 seconds to do each driver individualy in a loop going through 900,000 records to pull out just a few hundred.

Im beside myself, thanks again.

GO SQL

Michal