I have three tables as defined below.
Table Users UserID Name fred Fred Jones joe Joe Hill mike Mike green
Table Addresses UserID Type Address fred email fred@wherever.com fred web http://freds.website.com joe email joe@wherever.com mike email mike@wherever.com mike web http://mikes.website.com
Table Passwords UserID Password create_Date fred fredcurrent "latest timestamp" fred fredearlier "earlier timestamp" joe joecurrent "latest timestamp" mike mikecurrent "latest" mike mikeearlier "earlier"
Note that I didn't put actual dates in the create_Date column, please imagine that they exist. I would like a single select or view that gives me the following records where the current password is the one with the latest create_Date in the Passwords table. In other words, I want to see all addresses, but only the current password.
UserID Name Type Address Password fred Fred Jones email fred@wherever.com fredcurrent fred Fred Jones web http://freds.website.com fredcurrent joe Joe Hill email joe@wherever.com joecurrent mike Mike green email mike@wherever.com mikecurrent mike Mike green web http://mikes.website.com mikecurrent
In the end I would like only one roundtrip request to Oracle/SQL to get this recordset. It doesn't seem all that unreasonable on the surface, but the answer isn't apparent to me.
Requires Free Membership to View
Oracle/SQL? Are you writing an application that needs to be portable between Oracle and SQL Server? If so, you're in luck, the following solution will work in both databases.
First, let's write a simple join query:
select Users.UserID
, Users.Name
, Addresses.Type
, Addresses.Address
, Passwords.Password
from Users
inner
join Addresses
on Users.UserID = Addresses.UserID
inner
join Passwords
on Users.UserID = Passwords.UserID
As you no doubt discovered, this query exhibits cross join effects. Each UserID will have M*N rows, where M is the number of addresses and N is the number of passwords. All that's necessary here is simply to restrict the number N to be 1, i.e. so that each UserID to joins only one row from the Passwords table. This is accomplished by adding the following WHERE condition:
where Passwords.create_Date
= ( select max(create_Date)
from Passwords
where UserID = Users.UserID )
One caveat here is that even though the subquery can only return one value, there might be two rows for the same UserID with the same create_Date. This might happen if the user changes her password twice on the same day. If this is a possibility, you'll want to use a DATETIME datatype for the create_Date column, and not a DATE datatype.
This was first published in May 2005
Join the conversationComment
Share
Comments
Results
Contribute to the conversation