Resolving an ID from a result set

Okay, two admissions here, one is I'm sure this question has been answered and it has something to do with a join and the second is I'm not sure why I'm writing an application which requires complex SQL when I don't really know SQL.

Here's the problem, most likely it's cake walk, but I can't seem to find the answer on here;

I have a select which returns an ID and a sum of items from a table. I want to take the ID and resolve this as the actual name.

Here's the query;

select top 10 
    sum(elapsedTimeSeconds) as et, AppID 
from UsersAppLog 
where userid = 1 
  and appid <> 7 
  and CreatedOn > '01 Sep 2011' 
group by 
    AppID 
order by 
    et desc

so I get;

et, appid

and I want to l开发者_运维问答ookup "description" from the windowsapps table based on the returned appid.

Any help would be appreciated.

Phil.

Just add the column you need to the query and the GROUP BY clause:

SELECT TOP 10 
    SUM(u.elapsedTimeSeconds) AS et, u.AppID, wa.Description
FROM dbo.UsersAppLog u
INNER JOIN dbo.WindowsApps wa ON u.AppID = wa.AppID
WHERE
    u.userid = 1 
    AND u.appid <> 7 
    AND u.CreatedOn > '20110901'     -- use ISO-8601 format for extra safety!
GROUP BY 
    u.AppID, wa.Description 
ORDER BY
    et DESC

Not knowing your exact schema (it would help for the future to post those things!), I've guessed that you have an AppID in the WindowsApplications table, too - and I've assumed all your u.AppID will have a proper match - therefore the INNER JOIN

I would also recommend to always use the ISO-8601 date format (YYYYMMDD) - it's the only way to make sure your code will work regardless of the locale/regional/language setting on the SQL Server machine in question.

Something like this should do it assuming your WindowsApps table is a 1 to 1 mapping with the UsersAppLog on that AppID:

select  top 10 sum(UAL.elapsedTimeSeconds) as et, UAL.AppID, WA.Description
from UsersAppLog UAL
join WindowsApps WA on UAL.AppID = WA.AppID
where UAL.userid = 1 and UAL.appid <> 7 and UAL.CreatedOn > '01 Sep 2011' group by UAL.AppID, WA.Description order by et desc