Solved

Password table not joining right, and row returning as null

Posted on 2014-03-12
4
314 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
[X]
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
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL several years ago, it seemed like now was a good time to update it for object-oriented PHP.  This article does that, replacing as much as possible the pr…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …

717 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