[wdvltalk] Re: [SQL] Tricky join

[Howard Cheng]

Sorry about the double post -- I'd forgotten that my list settings were to 
not receive my own messages and I was sitting here going, what's wrong with 
the list?? :)

Anyway, I realize that the following statement works, but it's really kind 
of a kludge and I was hoping for something better:

SELECT um.userMovieID, um.userID, um.movieEditionID,
   (SELECT TOP 1 borrower_userID FROM MovieCheckouts mc1 WHERE 
mc1.userMovieID=um.userMovieID ORDER BY dateCheckedOut DESC) AS 
borrower_userID,
   (SELECT TOP 1 dateCheckedOut FROM MovieCheckouts mc1 WHERE 
mc1.userMovieID=um.userMovieID ORDER BY dateCheckedOut DESC) AS dateCheckedOut,
   (SELECT TOP 1 dateCheckedIn FROM MovieCheckouts mc1 WHERE 
mc1.userMovieID=um.userMovieID ORDER BY dateCheckedOut DESC) AS dateCheckedIn
FROM UsersMovies um
WHERE um.movieEditionID IN ([id list])

That's awfully unwieldy. Anyone got a better way? Thanks.

At 04:37 PM 1/22/2004, Howard Cheng wrote:
SELECT um.userMovieID, um.userID, um.movieID,
   t.borrower_userID, t.dateCheckedOut, t.dateCheckedIn
FROM UsersMovies um
LEFT JOIN (
   SELECT TOP 1 userMovieID, borrower_userID, dateCheckedOut, dateCheckedIn
   FROM MovieCheckouts mc
   WHERE mc.userMovieID=um.userMovieID
   ORDER BY dateCheckedOut DESC
) AS t
ON um.userMovieID = t.userMovieID
WHERE um.movieID IN ([id list goes here])




::::::::::::::::::::::::::::::::::
Howard Cheng
http://www.howcheng.com/
howcheng at ix dot netcom dot com
AIM: bennyphoebe
ICQ: 47319315 

____ • The WDVL Discussion List from WDVL.COM • ____
To Join wdvltalk, Send An Email To: mailto:[EMAIL PROTECTED] 
      Send Your Posts To: [EMAIL PROTECTED]
To set a personal password send an email to [EMAIL PROTECTED] with the words: "set WDVLTALK pw=yourpassword" in the body of the email.
To change subscription settings to the wdvltalk digest version:
   http://wdvl.internet.com/WDVL/Forum/#sub

________________  http://www.wdvl.com  _______________________

You are currently subscribed to wdvltalk as: [EMAIL PROTECTED]
To unsubscribe send a blank email to [EMAIL PROTECTED]
To unsubscribe via postal mail, please contact us at:
Jupitermedia Corp.
Attn: Discussion List Management
475 Park Avenue South
New York, NY 10016
Please include the email address which you have been contacted with.