"SQL is like voodoo, I swear!" We all knew that already.
-----Original Message----- From: Kay Smoljak [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 14, 2005 2:55 AM To: SQL Subject: Re: sub-select with an aggregate Hi Tim and Ray, Thanks for your help. I had the extra aliased tables because *I swear on my life* it wasn't AT ALL working without them. But I stripped it all back and lo and behold, what I originally thought would work (and of course what you were suggesting) does now. Typical. Final code: SELECT UserId, (SELECT TOP 1 MediaAccess.dtCreated FROM MediaAccess WHERE MediaAccess.UserID = MediaUser.UserId ORDER BY MediaAccess.dtCreated DESC) AS LastLogin, (SELECT COUNT(MediaAccess.UserId) FROM MediaAccess WHERE MediaAccess.UserID = MediaUser.UserId) AS TotalLogins FROM MediaUser SQL is like voodoo, I swear! Cheers, Kay. -- Kay Smoljak http://kay.zombiecoder.com/ On 9/13/05, Raster, Tim <[EMAIL PROTECTED]> wrote: > I tried similar queries here, and it works fine for me (SQL 2k). One > question... why do you have "MediaUser B" in your LastLogin part? I > don't see it referenced or joined up anywhere. Do you need it in > there, or is MediaAccess enough? (I fear you might be query > cross-producting and hurting performance) > > One other idea: you have MediaAccess showing up in both subqueries. > Perhaps try giving them aliases "ma1" and "ma2" or something, and > reference them that way. I know it shouldn't matter, but perhaps the > 2nd subquery is looking at "MediaAccess" from subquery 1 or something > dumb like that. > > > -----Original Message----- > From: Kay Smoljak [mailto:[EMAIL PROTECTED] > Sent: Monday, September 12, 2005 21:26 > To: SQL > Subject: sub-select with an aggregate > > Hi SQL Gurus! > > I have a query with two subselects - I'm getting a list of users with > the date of their last login and the total number of logins. The first > part - last login - is working but I can't seem to get the total > number of logins right - the same number gets returned for each row. I > think this might need the HAVING clause, but I'm not 100% sure how to > use it. SQL Server 2000, by the way... > > SELECT A.userID, A.name, > > /* this bit works */ > (SELECT TOP 1 MediaAccess.dtCreated > FROM MediaUser B, MediaAccess > WHERE MediaAccess.userID = A.userID > ORDER BY MediaAccess.dtCreated DESC) AS LastLogin, > > /* this bit returns the same number for everyone */ > (SELECT COUNT(MediaAccess.userID) > FROM MediaAccess > WHERE MediaAccess.userID = A.userID) AS TotalLogins > > FROM MediaUser A > > MediaAccess basically contains the userID, date of access and IP > address. > > So can anyone see the (really obvious, I'm sure) problem? > > Thanks! > > -- > Kay Smoljak > http://kay.zombiecoder.com/ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:6:2394 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/6 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:6 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.6 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
