Notification

Icon
Error

Software report, return one result from a group

Posted: Wednesday, January 27, 2021 10:13:34 AM(UTC)
MichaelC

MichaelC

Member Original PosterPosts: 2
0
Like
Hi.

I am fairly terrible at SQL, I am trying to relearn it after years away from it. So hopefully someone could help me, I would be very grateful.

What I am trying to do is search a specific group of assets by assetname and return an entry for each asset that has one of 5 possible programs. Querying tblSoftwareUni.softwareName
My problem is it is returning a result for each software found, so it will return 3 entries for the one asset if it has each of those programs installed. I tried using a Case as well but had the same result, I always assumed case would find the first entry and stop searching.

When I tried to use an In condition it returned zero results, so I had to go with Like Or.

Brick wall Brick wall

Select Top 100000 tblAssets.AssetID,
tblAssets.AssetName,
tsysOS.OSname As OS,
tsysOS.Image As Icon,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Lastseen,
Concat(tblSoftwareUni.softwareName, ' ', tblSoftware.softwareVersion) As NIMIS

From tblAssets
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID

Where (tblAssets.AssetName In ('Multiple asset names') And
tblSoftwareUni.softwareName Like '%Software1%') Or
(tblSoftwareUni.softwareName Like '%Software2%') Or
(tblSoftwareUni.softwareName Like '%Software3%') Or
(tblSoftwareUni.softwareName Like '%Software4%') Or
(tblSoftwareUni.softwareName Like '%Software5%' And
tblComputersystem.Domainrole <= 1)


Thanks
RC62N
#1RC62N Member Posts: 510  
posted: 1/27/2021 6:53:06 PM(UTC)
If all you want is a list of assets where any one of the pieces of software is installed but don't need the details of the software, take the software out of your main query and add a sub-query in your WHERE clause. See here and here for a couple of past examples. (They were looking for assets where something was missing, but you can use WHERE EXISTS (SELECT...) to check for the presence of software.)

If you're wanting to put all the found matching software together in a single column, take a look here. In that case it's multiple monitors, but that's easy enough to change to the software titles you want to find.
MichaelC
#2MichaelC Member Original PosterPosts: 2  
posted: 1/29/2021 3:52:47 PM(UTC)
That is brilliant thank you, I wanted to give the person working on the project as much information as possible, I hit a wall with all the software.

Active Discussions

Installer RDP - enable/disable - add/delete users
by  RobertoP   Go to last post Go to first unread
Last post: 2/9/2021 11:25:52 AM(UTC)
Installer Microsoft Edge (Chromium) 84.0.522.44
by  PLSJohnJohn  
Go to last post Go to first unread
Last post: 1/21/2021 11:46:38 PM(UTC)
Installer Install Tight VNC server
by  RobertoP   Go to last post Go to first unread
Last post: 12/16/2020 2:48:36 PM(UTC)
Installer Instal/Update Firefox to 84.0
by  RobertoP  
Go to last post Go to first unread
Last post: 12/16/2020 2:36:41 PM(UTC)
Installer Instal/Update Chrome to 87.0.4280.88
by  RobertoP   Go to last post Go to first unread
Last post: 12/16/2020 2:34:18 PM(UTC)
Installer Change IP ipv4
by  RobertoP  
Go to last post Go to first unread
Last post: 12/14/2020 9:21:14 AM(UTC)
Installer Update Adobe Acrobat Reader DC
by  RobertoP   Go to last post Go to first unread
Last post: 12/11/2020 2:44:29 PM(UTC)
Installer Instal Adobe Acrobat Reader DC
by  RobertoP  
Go to last post Go to first unread
Last post: 12/11/2020 2:41:48 PM(UTC)