Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    Regular Coder
    Join Date
    Dec 2004
    Location
    Jamaica
    Posts
    592
    Thanks
    2
    Thanked 0 Times in 0 Posts

    sql joins error please help!!!!

    Code:
        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!
    I'm gonna find a way to download the internet if its the last thing I do...
    Prepare to bow down to me (or my grave) and call me almighty when the algorithm is finished

  • #2
    teh Moderatorinator
    Join Date
    Sep 2004
    Location
    USA
    Posts
    2,472
    Thanks
    4
    Thanked 40 Times in 40 Posts
    Try this:
    Code:
     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 & "'"

  • #3
    Regular Coder
    Join Date
    Dec 2004
    Location
    Jamaica
    Posts
    592
    Thanks
    2
    Thanked 0 Times in 0 Posts
    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!

    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 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.


    I'm gonna find a way to download the internet if its the last thing I do...
    Prepare to bow down to me (or my grave) and call me almighty when the algorithm is finished

  • #4
    teh Moderatorinator
    Join Date
    Sep 2004
    Location
    USA
    Posts
    2,472
    Thanks
    4
    Thanked 40 Times in 40 Posts
    Yes, you can have more than one join...but you can try this:
    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 & "'"

  • #5
    Regular Coder
    Join Date
    Dec 2004
    Location
    Jamaica
    Posts
    592
    Thanks
    2
    Thanked 0 Times in 0 Posts

    The problem complexity now squared

    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?
    I'm gonna find a way to download the internet if its the last thing I do...
    Prepare to bow down to me (or my grave) and call me almighty when the algorithm is finished

  • #6
    Regular Coder
    Join Date
    Dec 2004
    Location
    Jamaica
    Posts
    592
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Hey Brando, here's the latest.... i still get an error for tha code:

    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:
    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?
    Last edited by jaywhy13; 02-03-2005 at 12:45 AM.
    I'm gonna find a way to download the internet if its the last thing I do...
    Prepare to bow down to me (or my grave) and call me almighty when the algorithm is finished


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •