[SOLVED] Sql query with count() join and where on to tables

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)

Leave a Reply

Your email address will not be published.