Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Password table not joining right, and row returning as null

Posted on 2014-03-12
4
309 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 109

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 40

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

790 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