Issue
I have two talbles a login table and a login attempts table for track worng login attempts
login table
| id | username | password |
| 1 | Jon | 1234 |
| 2 | Danny | ttttt |
| 7 | Martin | fffff |
attempts table
| id_attempts| time |
| 1 | 535353 |
| 2 | 554335 |
| 1 | 654545 |
| 1 | 566777 |
Query must identify a user as authorized, check the database for his combination of username/password but must return also the number of incorrect attempts for that user, even if the username and password are wrong.
I’have try this:
SELECT u.id,count(p.id_attempts)
FROM login as l, attempts as a
LEFT JOIN
ON l.id=a.id_attempts
WHERE username=Jon
AND pass=1234
EDITED:
Exmple Jon try to login, if username and password are correct query must return jon id and 3 (number of jon wrong attempt) if jon username and password are wrong query must return only 3 (number of jon wrong attempt)
Solution
Instead of “if jon username and password are wrong query must return only 3 (number of jon wrong attempt)” I recommend using an additional column instead to return the authorization status. e.g.
Also, since the username is being specified in the query, there is not much point in returning the userid or username.
SELECT l.username, count(l.id) attempts, l.password='1234' authorized
FROM login as l LEFT JOIN attempts as a
ON l.id=a.id_attempts
WHERE l.id=1
GROUP BY l.username
will return:
username,attempts,authorized
Jon,3,1
Where 1 means authorized, and 0 not authorized.
But if you really want to meet your original requirement, do this:
SELECT IF( l.password = '1234' && l.id =1, l.id, "" ) id, COUNT( l.id ) attempts
FROM login AS l
LEFT JOIN attempts AS a ON l.id = a.id_attempts
WHERE l.id =1
GROUP BY l.username
For a correct password, this will return:
id,attempts
1,3
Where there was no correct password, this will return:
id,attempts
,3
Answered By – George O.
Answer Checked By – Dawn Plyler (BugsFixing Volunteer)