?
Solved

Password table not joining right, and row returning as null

Posted on 2014-03-12
4
Medium Priority
?
328 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 111

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 26

Accepted Solution

by:
Tomas Helgi Johannsson earned 2000 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

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

839 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