SitePoint Sponsor

User Tag List

Results 1 to 16 of 16
  1. #1
    SitePoint Evangelist
    Join Date
    Apr 2005
    Location
    London, UK
    Posts
    505
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL query issue

    Hi Guys,

    Table Authenticate:

    idAuthenticate | username | password
    1 john letmein

    Table Client:

    idClient | Enabled | idAuthenticate
    1 Yes 1

    My query is returning zero if say somethin like this:

    Code:
    SELECT
    authenticate.idAuthenticate,
    authenticate.username, 
    authenticate.password,
    client.idClient,
    client.Enabled
    
    FROM
    authenticate
    
    LEFT JOIN
    client ON authenticate.idAuthenticate = client.idClient
    
    WHERE
    authenticate.username = 'john'
    
    AND
    authenticate.password = 'letmein'
    
    AND
    client.Enabled = 'Yes';
    but works if I remove client.Enabled = 'Yes'; from the query. so my query is not right, does anyone know how to do it?

    Thanks

  2. #2
    SitePoint Guru Jason__C's Avatar
    Join Date
    Oct 2009
    Location
    Racoon City
    Posts
    656
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Is this query in the .php page, or straight from the DB? You have a semicolon on the last statement, and if you we're running it out of the webpage, that would cause an error. Then again, I could be wrong.

  3. #3
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,401
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    Put any conditions on the left joined table in the ON clause for that join.
    If you put that condition in the WHERE clause, the left join becomes an inner join (at least as far as the results of the query are concerned).

  4. #4
    SitePoint Guru Jason__C's Avatar
    Join Date
    Oct 2009
    Location
    Racoon City
    Posts
    656
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    Put any conditions on the left joined table in the ON clause for that join.
    If you put that condition in the WHERE clause, the left join becomes an inner join (at least as far as the results of the query are concerned).
    Most likely your right, but wouldn't the semicolon also cause an error? For future reference...

  5. #5
    SitePoint Evangelist
    Join Date
    Apr 2005
    Location
    London, UK
    Posts
    505
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    Put any conditions on the left joined table in the ON clause for that join.
    If you put that condition in the WHERE clause, the left join becomes an inner join (at least as far as the results of the query are concerned).
    Hey Guido,

    I'm not sure about the syntax, how would I do that, can you care to show me please?

  6. #6
    SitePoint Evangelist
    Join Date
    Apr 2005
    Location
    London, UK
    Posts
    505
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by USPaperchaser View Post
    Most likely your right, but wouldn't the semicolon also cause an error? For future reference...
    the semicolon is there because I copied straight from MySQl Query Browser

  7. #7
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,401
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    Code:
    SELECT
        authenticate.idAuthenticate
      , authenticate.username
      , authenticate.password
      , client.idClient
      , client.Enabled
    FROM
        authenticate
    LEFT JOIN
        client 
    ON 
        authenticate.idAuthenticate = client.idClient
    AND
        client.Enabled = 'Yes'
    WHERE
        authenticate.username = 'john'
    AND
        authenticate.password = 'letmein'

  8. #8
    SitePoint Evangelist
    Join Date
    Apr 2005
    Location
    London, UK
    Posts
    505
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    Code:
    SELECT
        authenticate.idAuthenticate
      , authenticate.username
      , authenticate.password
      , client.idClient
      , client.Enabled
    FROM
        authenticate
    LEFT JOIN
        client 
    ON 
        authenticate.idAuthenticate = client.idClient
    AND
        client.Enabled = 'Yes'
    WHERE
        authenticate.username = 'john'
    AND
        authenticate.password = 'letmein'
    ahhh that easy it was, I just was not sure when said move it in the ON clause, but this works now.

    Thanks v much.

  9. #9
    SitePoint Evangelist
    Join Date
    Apr 2005
    Location
    London, UK
    Posts
    505
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Guido, sorry man.

    This actually is not working. If Enabled = 'No', the query should return zero, but instead it returns 1 match, so I think the moving of AND Enabled = 'Yes' has made the query discard that condition.

  10. #10
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,401
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    It all depends on what you want.
    You are using a left join, this means it'll always return the rows found in the first table, regardless of what is found in the second table. If no corresponding row is found in the second table, then those columns will be NULL.

    So why don't you explain in plain english (not query) what you're trying to do here?

  11. #11
    SitePoint Evangelist
    Join Date
    Apr 2005
    Location
    London, UK
    Posts
    505
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    It all depends on what you want.
    You are using a left join, this means it'll always return the rows found in the first table, regardless of what is found in the second table. If no corresponding row is found in the second table, then those columns will be NULL.

    So why don't you explain in plain english (not query) what you're trying to do here?
    Ok I want to authenticate a user login where password and username match and enabled = yes.

  12. #12
    SitePoint Evangelist
    Join Date
    Apr 2005
    Location
    London, UK
    Posts
    505
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, so I figuerd I should do a RIGHT JOIN, this seems to work.

  13. #13
    SitePoint Evangelist
    Join Date
    Apr 2005
    Location
    London, UK
    Posts
    505
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey Guido,

    I might be taking the miki out of you, but would you explain how the LEFT and RIGHT JOIN work for my specific tables. I just dont get the exact picture.

  14. #14
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,401
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by ma201dq View Post
    Ok I want to authenticate a user login where password and username match and enabled = yes.
    Use an INNER JOIN then.
    If you don't get any rows with enabled = 'Yes', and you do eliminating that check, then check the value of the enabled column in the clients table. Probably it isn't 'Yes', but 'yes' or something like that

    Edit: check your join criteria as well: you are joining on client.idClient. Shouldn't that be client.idAuthenticate ?

  15. #15
    SitePoint Evangelist
    Join Date
    Apr 2005
    Location
    London, UK
    Posts
    505
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    Use an INNER JOIN then.
    If you don't get any rows with enabled = 'Yes', and you do eliminating that check, then check the value of the enabled column in the clients table. Probably it isn't 'Yes', but 'yes' or something like that

    Edit: check your join criteria as well: you are joining on client.idClient. Shouldn't that be client.idAuthenticate ?
    thanks guido, yeah i already noticed the Edit you mentioned. The RIGHT JOIN works just as good.

  16. #16
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,401
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by ma201dq View Post
    Hey Guido,

    I might be taking the miki out of you, but would you explain how the LEFT and RIGHT JOIN work for my specific tables. I just dont get the exact picture.
    Let me 'lend' some useful links from another post:

    SitePoint Forums - View Single Post - blog database design
    Coding Horror: A Visual Explanation of SQL Joins

    These should explain joins a bit better


Bookmarks

Posting Permissions

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