Report on Assets with Office 2010 with most recent Comments

[russtilling]

Hi,

I'm trying to create a report with AssetName, SoftwareName, Lastseen, Comment, AddedBy, for all assets with Office 2010 or Office Pro Plus 2010.
My attempt below does not show assets with blank comments, and duplicates rows with multiple comments. I only want the most recent comment and to include assets with no comments

Select Top 999 tblAssets.AssetName,
tblSoftwareUni.softwareName,
tblAssets.Lastseen,
tblAssetComments.Comment,
tblAssetComments.AddedBy
From dbo.tblAssets
Inner Join dbo.tblSoftware On tblSoftware.AssetID = tblAssets.AssetID
Inner Join dbo.tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
Inner Join dbo.tblAssetComments On tblSoftware.AssetID =
tblAssetComments.AssetID
Where (tblSoftwareUni.softwareName Like N'%Microsoft Office Standard 2010%' Or
tblSoftwareUni.softwareName Like
N'%Microsoft Office Professional Plus 2010%')
Order By tblAssets.AssetName

[rom · Accepted solution]

Hm. I don't know how to use this new forum very well, I can't find the SQL code option... (shrug)

your comments join was INNER join, which means a 'match' query - so if an asset has no comment record, its a no-match and it won't be returned in the results.  change it to LEFT join so that way, the primary source is the asset table, and if it doesn't have a comment record, it just returns NULL.  There's a bunch of ways to do the MAX/latest comment, but i'll do it the down-and-dirty way, just put max(whatever) and add the other columns to the GROUP BY clause.

Select Top 999 tblAssets.AssetName,
tblSoftwareUni.softwareName,
tblAssets.Lastseen,
max(tblAssetComments.Comment) as LatestComment,
tblAssetComments.AddedBy
From dbo.tblAssets
Inner Join dbo.tblSoftware On tblSoftware.AssetID = tblAssets.AssetID
Inner Join dbo.tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
left join tblAssetComments on tblAssets.AssetID =tblAssetComments.AssetID
Where (tblSoftwareUni.softwareName Like N'%Microsoft Office Standard 2010%' Or
tblSoftwareUni.softwareName Like
N'%Microsoft Office Professional Plus 2010%')
Group by tblassets.AssetName, tblassets.Lastseen, tblSoftwareUni.softwareName, tblAssetComments.AddedBy
Order By tblAssets.AssetName

[russtilling]

Thank you so much Rom! 😍😀

[russtilling]

I did find this Solved: Re: Get last comment from asset - Lansweeper Community - 19149
But couldn't adapt it to being a newbie to LS and not great at SQL either.
Thanks