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/ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:6:2390 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
