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

Question

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)

Answer 1

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

Answer 2

I don't know which database engine you are using since you have specified both mysql and postgresql as tags but here is how the select statement looks like in PostgreSQL:

SELECT (CASE WHEN l.password = ? THEN l.id END) AS userId,
  COUNT(a.id_attempts) AS numAttempts
FROM login l LEFT JOIN attempts a ON a.id_attempts=l.id
WHERE l.username = ?
GROUP BY l.id;

Answer 3

I'm not sure how you can tell whether or not an attempt was successful from your example but this gives you count of attempts

select 
  l.username, 
  count(1) as NumAttempts
from login l
join attempts a 
  on l.id = a.id_attempts
where l.id = 1
  and l.password = '1234'
group by l.username