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`.`username` = '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:-
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))

Open in new window


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`.`Changed`), but the password is wrong (`_Users_Passwords`.`Password`).

Any ideas
tonelm54Asked:
Who is Participating?
 
Tomas Helgi JohannssonConnect With a Mentor Commented:
Hi!

Try this

SELECT u.*,  h.*, h.`Changed`, up.`Password`
FROM `_users` u, `_users_helpdesk` h, `_Users_Passwords` up
WHERE
u.`userID` = h.`userID`
and u.`userID` = up.`userID`
and ((up.`Password` = 'myPassword') AND
((h.`username` = 'myUsername') OR (u.`username` = 'myUsername')) AND (h.`loginEnabled` = true))
and up.`Changed` = (select  max(upp.`Changed`) from `_Users_Passwords` upp where upp.`userID` = u.`userID` )

Open in new window


Regards,
   Tomas Helgi
0
 
Ray PaseurCommented:
How do you define "latest passwords?"

Please post the CREATE TABLE statements for the table(s) involved, thanks.
0
 
c_kedarCommented:
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
;

Open in new window


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.
0
 
SharathData EngineerCommented:
Provide some sample data and expected result like your other question
http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_28386782.html
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.