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:2389
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
