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/
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Stay Ahead of Hackers - Download ZoneAlarm Pro
http://www.houseoffusion.com/banners/view.cfm?bannerid=65
Message: http://www.houseoffusion.com/lists.cfm/link=i:6:2393
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
