Joins error.... please help!

jaywhy13 · February 2nd, 2005, 08:52 PM

I get the following error for this code....
Please help:
============================
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'users.username=userPreferences.username INNER JOIN displayOptions ON userPreferences.displayID=displayOptions.displayID '.

And the error is on the line where i do this:
RS.Open sqlAuthenticate, Conn, 3, 3
My RS is defined eariler

Code:

sqlAuthenticate="SELECT userPreferences.msgSort, userPreferences.msgDirection, 
users.username, users.password, users.picLocation, users.firstname FROM users 
LEFT JOIN userPreferences ON users.username=userPreferences.username INNER JOIN
displayOptions on userPreferences.displayID = displayOptions.displayID
WHERE users.username='" & user & "' AND users.password='" & password & "'"

I'm gonna download the internet if its the last thing I do....
Until the algorithm is finished u must prepared to bow down and worship me (or my grave).... i figured i'll b finished b4 the latter!

om_prakash · February 2nd, 2005, 10:30 PM

can u response.write the SQL statement?

Also, please check for the Access driver as well. this might be an issue.

Om Prakash

mat41 · February 2nd, 2005, 10:53 PM

mmmm, I can not see a missing operator

Wind is your friend
Matt

ChrisScott · February 3rd, 2005, 05:02 AM

Looks like it might be access's love of brackets around various joins that's the problem, also I think it may complain when you try to do a left join before an outer join.
Give this a try...

Code:

sqlAuthenticate="SELECT userPreferences.msgSort, userPreferences.msgDirection, 
users.username, users.password, users.picLocation, users.firstname FROM (users 
LEFT JOIN userPreferences ON users.username=userPreferences.username) LEFT JOIN
displayOptions on userPreferences.displayID = displayOptions.displayID
WHERE users.username='" & user & "' AND users.password='" & password & "';"

HTH,

Chris

jaywhy13 · February 3rd, 2005, 09:12 AM

thanks! That worked.... I wanted to really use an INNER JOIN for the last one but the LEFT JOIN will work still because there are matchin records for all.... so it still acts like a INNER JOIN tho thanks!

I'm gonna download the internet if its the last thing I do....
Until the algorithm is finished u must prepared to bow down and worship me (or my grave).... i figured i'll b finished b4 the latter!