tonelm54
asked on
Password table not joining right, and row returning as null
Im trying to join 3 tables, however the table named `_Users_Passwords` I need to pull only the row with the latest passwords.
The difficult part is the username password can be stored in 2 tables (either the deault username - `_users`.`username` or the helpdesk username - `_users_helpdesk`.`usernam e` = 'myUsername'), the passwords table stores all historic passwords, so I need to pull only the newest password, identified by the date.
What Ive got soo far is:-
I have 2 issues with my code Id appriciate someones help with.
1- If myUsername and myPassword are wrong then it will still return a row of nulls.
2- If I remove the password checking (so I can check whats going on), the date of the last password change is correct (`_Users_Passwords`.`Chang ed`), but the password is wrong (`_Users_Passwords`.`Passw ord`).
Any ideas
The difficult part is the username password can be stored in 2 tables (either the deault username - `_users`.`username` or the helpdesk username - `_users_helpdesk`.`usernam
What Ive got soo far is:-
SELECT `_users`.*, `_users_helpdesk`.*, max(`_Users_Passwords`.`Changed`), `_Users_Passwords`.`Password`
FROM `_users`
INNER JOIN `_users_helpdesk` ON `_users`.`userID` = `_users_helpdesk`.`userID`
INNER JOIN `_Users_Passwords` ON `_users`.`userID` = `_users_Passwords`.`userID`
WHERE
((`_Users_Passwords`.`Password` = 'myPassword') AND
((`_users_helpdesk`.`username` = 'myUsername') OR (`_users`.`username` = 'myUsername')) AND
(`_users_helpdesk`.`loginEnabled` = true))
I have 2 issues with my code Id appriciate someones help with.
1- If myUsername and myPassword are wrong then it will still return a row of nulls.
2- If I remove the password checking (so I can check whats going on), the date of the last password change is correct (`_Users_Passwords`.`Chang
Any ideas
select x.*, `_Users_Passwords`.`Password`
FROM
(
SELECT `_users`.`userID`, max(`_Users_Passwords`.`Changed`) lastChanged
FROM `_users`
INNER JOIN `_users_helpdesk` ON `_users`.`userID` = `_users_helpdesk`.`userID`
INNER JOIN `_Users_Passwords` ON `_users`.`userID` = `_users_Passwords`.`userID`
WHERE
((`_Users_Passwords`.`Password` = 'myPassword') AND
((`_users_helpdesk`.`username` = 'myUsername') OR (`_users`.`username` = 'myUsername')) AND
(`_users_helpdesk`.`loginEnabled` = true))
GROUP BY `_users`.`userID`
) x
INNER JOIN `_Users_Passwords` ON
x.`userID` = `_users_Passwords`.`userID` and
`_Users_Passwords`.`Changed` = x.lastChanged
;
I did not understand your issue #1 really, but I guess it is because of missing group by clause in your SQL.
In standard (i.e. ANSI) SQL, we have to do this in two steps, first the get the max change date and then get the password on that date - this is what is done in solution given above.
It is possible to do this without having to use inner query, using MySQL specific funtions GROUP_CONCAT and SUBSTRING_INDEX, but it would little complex to understand.
Provide some sample data and expected result like your other question
https://www.experts-exchange.com/questions/28386782/Show-only-latest-row-from-Join.html
https://www.experts-exchange.com/questions/28386782/Show-only-latest-row-from-Join.html
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Please post the CREATE TABLE statements for the table(s) involved, thanks.