JOIN sql statement works in Access but not dbf

[charlotte49er]

I am trying to join together 3 separate dbf files (common field is SITE_ID) using the following sql statement in an asp page:

strSQL = "SELECT extents.*, usgsdata.*, usgsgage.* FROM usgsgage.dbf INNER JOIN (extents.dbf INNER JOIN usgsdata.dbf ON extents.SITE_ID = usgsdata.SITE_ID) ON usgsgage.SITE_ID = usgsdata.SITE_ID;"

I haven't had much luck so I imported these tables into an Access db, removed the file extensions, and ran the sql statement from the asp page and it worked! (on a side note, if I just run the join on two of the dbf files it works). Since these are dbf files and I need to update them I am using the Visual FoxPro driver. I am guessing that there is something peculiar in the driver. Can anyone help me out? Unfortunately, we have no other choice than to use these dbf files. Thanks.

 

[GriffMG]

I don't generally (i.e. I haven't) included the file extensions in my asp code - try it without them first.

After that, I'd start by simplifying the statement to a point where it worked (i.e. start with one table) and then add the joins one by one to find the problems.

Regards

Griff
Keep [Smile]ing

 

[GriffMG]

Looking on the MS site for details, there doesn't seem to be support for an explicit 'inner join' in the VFP ODBC

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/lr30b_8.asp

It might work swapping for a simple 'where' statement:

strSQL = "SELECT extents.*, usgsdata.*, usgsgage.* FROM usgsgage, usgsdata, extents where extents.SITE_ID = usgsdata.SITE_ID and usgsgage.SITE_ID = usgsdata.SITE_ID;"

I'm 100% sure that will get what you want though!

Regards

Griff
Keep [Smile]ing

 

[GriffMG]

Sorry to prattle on, but I've just been looking at SQL statements and Inner Join - the where statement used in this context SHOULD (from my reading) be the same as your inner join!

Good luck

Regards

Griff
Keep [Smile]ing

 

[Stella740pl]

charlotte49er,

You can also try this:

strSQL = "SELECT extents.*, usgsdata.*, usgsgage.* FROM usgsgage INNER JOIN extents ON extents.SITE_ID = usgsgage.SITE_ID INNER JOIN usgsdata ON usgsdata.SITE_ID = usgsgage.SITE_ID;"

Stella

 

[craigsboyd]

GriffMG,

Yeah, only difference to my understanding is one is called an INNER JOIN and the other using the where clause is called an EQUI-JOIN. They are interchangeable in this context.

Slighthaze = NULL
_{craig1442@mchsi.com}

"Whom computers would destroy, they must first drive mad." - Anon​

 

[craigsboyd]

I've even seen it called a "natural join" so,

"INNER JOIN" = "EQUI-JOIN" = "NATURAL JOIN" = "Pick a name for it already!"

Slighthaze = NULL
_{craig1442@mchsi.com}

"Whom computers would destroy, they must first drive mad." - Anon​