...

View Full Version : sql joins error please help!!!!



jaywhy13
01-30-2005, 02:39 PM
sqlAuthenticate="SELECT users.username, users.password, users.picLocation, users.firstname FROM users, userPreferences LEFT JOIN userPreferences ON users.username=userPreferences.username WHERE users.username='" & user & "' AND users.password='" & password & "'"

I get the error:
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error in JOIN operation.


Please help....
I have two tables. A users table that holds the firstname, username and password. The second table contains the user Preferences... the user may/may not have preferences i.e. may or may not have a corresponding id in the preferences table so I need to perform a left join... so that it will show up the records in the users table even if there is no match in the preferences table.

All help will b appreciated!

Brandoe85
01-30-2005, 07:26 PM
Try this:


sqlAuthenticate="SELECT users.username, users.password, users.picLocation, users.firstname FROM users LEFT JOIN userPreferences ON users.username=userPreferences.username WHERE users.username='" & user & "' AND users.password='" & password & "'"

jaywhy13
01-31-2005, 12:10 PM
Okay thanks for ur help thus far..... but I need to complicate this example some more to achieve wot I need to achieve. Can I make like two joins or so? How do i include another table in the result of this query?

Here's the low down.
Three tables:
users - contains the user data (username pk)
userPreferences - contains certain preferences includin the username (may or may not have a record for the user.... so i need to do a left join for users). This table also includes a displayID which links to the following table...
displayOptions - this contains some display options and it has the displayID as pk. So this table will link with the previous. There WILL be a corresponding key in the userPreferences table for each displayID so yeah..... this should b an INNER JOIN.


How do I get that done?
So far this is wot I've got!


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

I've already got me my left join between users and userPreferences.... now I need to include an inner join between userPreferences and a table called displayOptions based on the displayID.


:thumbsup:

Brandoe85
02-01-2005, 12:56 AM
Yes, you can have more than one join...but you can try this:


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 & "'"

jaywhy13
02-01-2005, 03:04 AM
I'm gonna try that and see how it works and I will definitely get back to ya!

But in the mean time... consider this: another method where I think that JOIN can definitely help me out.

I have a main table lets call it "alldata"
now alldata will take entries from two types of persons... members of the public and users of the system

Now if a user enters data into this table... a field in the table, call it "type" will be "user" and if a member of the public has entered then.... it will store "public"

Now all user data is stored in a table called "users" and all data on members of the public who come in are stored in "publicData"

So you see now.... I have one table "alldata" and two others tables described above.
What I have done which i am really embarassed to admit but cannot deny is its high inefficiency.

Open the "alldata" table and drop it in getRows
link alldata with users (for type "user") and drop that in a getRows
link alldata with public (for type "public") and drop that in yet another getRows

Then i would scroll thru the "alldata" getrows and check its type property... then decide whether i needed the data from the publicgetrows or the usergetrows.... And i would hav a lil pointer that increments each time i use data from either public/user getrows

So I am open to corrections & suggestion
Get all that done with ONE sql statement?

jaywhy13
02-03-2005, 12:32 AM
Hey Brando, here's the latest.... i still get an error for tha 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've even tried editing and rewriting from scratch..... i have the code:

sqlAuthenticate="SELECT 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 & "'"


Oh and the painstaking error....
============================
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



Got any suggestions on the post prior to this one anyone?



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum