MySQL query issue

Databases
1

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:


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

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

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

Most likely your right, but wouldn’t the semicolon also cause an error? For future reference…

5

Hey Guido,

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

6

the semicolon is there because I copied straight from MySQl Query Browser :slight_smile:

7 

SELECT
    authenticate.idAuthenticate
  , authenticate.username
  , authenticate.password
  , client.idClient
  , client.Enabled
FROM
    authenticate
LEFT JOIN
    client 
ON 
    authenticate.idAuthenticate = client.idClient
[B][COLOR="Red"]AND
    client.Enabled = 'Yes'[/COLOR][/B]
WHERE
    authenticate.username = 'john'
AND
    authenticate.password = 'letmein'
8

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

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

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

Ok I want to authenticate a user login where password and username match and enabled = yes.

12

Ok, so I figuerd I should do a RIGHT JOIN, this seems to work.

13

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

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 :slight_smile:

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

15

thanks guido, yeah i already noticed the Edit you mentioned. The RIGHT JOIN works just as good.

16

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 :slight_smile: