Solved

Password table not joining right, and row returning as null

Posted on 2014-03-12
4
311 Views
Last Modified: 2014-03-29
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
0
Comment
Question by:tonelm54
4 Comments
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 39924957
How do you define "latest passwords?"

Please post the CREATE TABLE statements for the table(s) involved, thanks.
0
 
LVL 2

Expert Comment

by:c_kedar
ID: 39926062
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
 
LVL 41

Expert Comment

by:Sharath
ID: 39927382
Provide some sample data and expected result like your other question
http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_28386782.html
0
 
LVL 25

Accepted Solution

by:
Tomas Helgi Johannsson earned 500 total points
ID: 39936832
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

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question