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 firstname.lastname@example.org fred web http://freds.website.com joe email email@example.com mike email firstname.lastname@example.org 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 email@example.com fredcurrent fred Fred Jones web http://freds.website.com fredcurrent joe Joe Hill email firstname.lastname@example.org joecurrent mike Mike green email email@example.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.
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