#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    4
    Rep Power
    0

    Join two tables and find unique user


    Hi there, hopefully someone knows the answer to this one:

    I have a blog with a personal page where users can edit their information.
    On this page I need to sort out the unique logged in user in a recordset and at the same time join information from another table.

    Have two tables, one for user login info - Users
    and one for contactinformation - Contact

    Have tried this:

    Code:
    SELECT *
    FROM Users, Contact
    WHERE Users.ID=Contact.UserID AND Username = MMColParam
    Users.ID and Contact.UserID are both PK

    No luck, just getting an empty return.

    Using Dreamweaver, Access and Asp.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    try like this first --
    Code:
    SELECT *
      FROM Users
    INNER
      JOIN Contact
        ON Contact.UserID = Users.ID
    when that works, then you can add the filter condition --
    Code:
    SELECT *
      FROM Users
    INNER
      JOIN Contact
        ON Contact.UserID = Users.ID
     WHERE users.Username = MMColParam
    breaking it up into two steps for testing purposes will isolate where the problem lies
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    4
    Rep Power
    0
    Hi r937, and thank you for replying!

    I run your suggestion and the first option returns all the values,
    but the second option returns nothing.


    Any clues?

    Regards,
    Chrvik
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by chrvik
    Any clues?
    it means there is not row in the users table with Username = MMColParam
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    4
    Rep Power
    0
    Ok, but the names of the tables and rows are correct.
    Doblechecked.

    Does that mean that the problem resides in the page coding, -and that the SQL should be fine?
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    the sql is fine, assuming that this bit --
    Code:
    = MMColParam
    actually results in a valid string getting inserted in that spot by dreamweaver and/or asp
    Code:
    = 'Todd'
    if it doesn't accomplish that, then the query should/would fail

    if it does accomplish that, then perhaps there is no Todd in the user table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    4
    Rep Power
    0
    I tested the following SQL:

    Code:
    SELECT *
    FROM Users
    WHERE Username = MMColParam
    And this returns the valid Username when live testing on the page, but when I try to combine this with a JOIN that's when the error appears and I get this error message:

    Code:
    ADODB.Fields error '800a0cc1'
    
    Item cannot be found in the collection corresponding to the requested name or ordinal.
    
    /mypage.asp, line 242
    Line 242 points directly to the recordset with the JOIN function.

    I have ordered a book called Simply SQL from Sitepoint, hopefully it arrives soon
  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    run the join query right in access, not via your web page (and, of course, hardcode a username)

    that'll tell you if it's working properly
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo