I would eliminate the aliasing as they may be causing the confusion.
SELECT MediaUser.userID,
MediaUser.name,
/* this bit works */
(SELECT TOP 1 dtCreated
FROM MediaAccess
WHERE MediaAccess.userID = MediaUser.userID
ORDER BY dtCreated DESC) AS LastLogin,
/* this bit returns the same number for everyone */
(SELECT COUNT(userID)
FROM MediaAccess
WHERE MediaAccess.userID = MediaUser.userID) AS TotalLogins
FROM MediaUser
Ray Thompson
Tau Beta Pi (www.tbp.org)
The Engineering Honor Society
865-546-4578
-----Original Message-----
From: Raster, Tim [mailto:[EMAIL PROTECTED]
Sent: Tuesday, September 13, 2005 10:13 AM
To: SQL
Subject: RE: sub-select with an aggregate
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:2391
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=11502.10531.6
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
