> 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.
