> Any ideas? Thanks. you sure that's sql server, not access?
when did sql server start using autoincrement? isn't it identity? as far as the syntax is concerned, i can see what you're trying to do in the subquery, you've got "um" as a correlation variable in there, and yet you are trying to do a join to the same subquery as a derived table i'm not sure i can explain it, but that won't work try it like this -- select um.userMovieID, um.userID, um.movieID , mc.borrower_userID, mc.dateCheckedOut, mc.dateCheckedIn from UsersMovies um left outer join MovieCheckouts mc on um.userMovieID = mc.userMovieID and mc.dateCheckedOut = ( select max(dateCheckedOut) from MovieCheckouts where userMovieID = mc.userMovieID ) where um.movieID IN ([id list goes here]) that uses a correlated subquery too, but what this one does is group the movies in the mc table by userMovieID, and take the latest, and use that as the row to get in the left outer join not sure i can explain that, either let me know if it works rudy ____ • 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.