I have the following two queries.
The first lists users and last login dates like this:
iUserNum chDefaultLogin dateLastLogin
-------- -------------- -------------
17 fred.bloggs 01/06/2011
23 john.doe 13/05/2011
using the following query
SELECT SDToken.iUserNum, chDefaultLogin, dateLastLogin
FROM SDUserScope JOIN SDToken ON SDUserScope.iUserNum = SDToken.iUserNum
The second one gives a count of the number of times a user has logged in (given the iUserNum user number) during the last 7 days using this query (in the example below I have given the iUse开发者_如何学编程rNum of 17):
SELECT COUNT(*) FROM SDToken
JOIN SDLogEntry ON SDLogEntry.chTokenSerialNum = SDToken.chSerialNum
JOIN SDUserScope ON SDToken.iUserNum = SDUserScope.iUserNum
JOIN SDLogMessage ON SDLogEntry.iMessageNum = SDLogMessage.iMessageNum
WHERE SDToken.iUserNum = 17 AND SDUserScope.iSiteNum = iMySite
AND SDLogEntry.dtGMTDate > GMTDateNow - 7
What I want to do is join the two together, so I get the count for every user in the table, like this:
iUserNum chDefaultLogin dateLastLogin loginCount
-------- -------------- ------------- ----------
17 fred.bloggs 01/06/2011 12
23 john.doe 13/05/2011 4
Have you try to unify the queries and just add a Group by ?
SELECT SDToken.iUserNum,
chDefaultLogin,
dateLastLogin,
COUNT(*) AS loginCount
FROM SDUserScope
INNER JOIN SDToken
ON SDUserScope.iUserNum = SDToken.iUserNum
INNER JOIN SDLogEntry
ON SDLogEntry.chTokenSerialNum = SDToken.chSerialNum
INNER JOIN SDLogMessage
ON SDLogEntry.iMessageNum = SDLogMessage.iMessageNum
WHERE SDToken.iUserNum = 17
AND SDUserScope.iSiteNum = iMySite
AND SDLogEntry.dtGMTDate > GMTDateNow - 7
GROUP BY SDToken.iUserNum,
chDefaultLogin,
dateLastLogin
2 subqueries joining on the username as common key should do the job in MS Sql server /SYBASE it will lokk like below but any other RDBMS you can apply same logic using 2 subqueries...syntax might differ a little buit
Select A.SDToken.iUserNem, A.chDefaultLogin, A.dateLastLoginm,B.logincount
FROM
(
(SELECT SDToken.iUserNem, chDefaultLogin, dateLastLogin FROM SDUserScope JOIN SDToken ON SDUserScope.iUserNum = SDToken.iUserNum )A
INNER JOIN
(SELECT COUNT(*)logincount,SDToken.iUserNem FROM SDToken
JOIN SDLogEntry ON SDLogEntry.chTokenSerialNum = SDToken.chSerialNum
JOIN SDUserScope ON SDToken.iUserNum = SDUserScope.iUserNum
JOIN SDLogMessage ON SDLogEntry.iMessageNum = SDLogMessage.iMessageNum
WHERE SDToken.iUserNum = 17 AND SDUserScope.iSiteNum = iMySite AND SDLogEntry.dtGMTDate > GMTDateNow - 7
GROUP BY SDToken.iUserNem)B
ON A.iUserNem = B.iUserNem
)
精彩评论