Software report, return one result from a group

[Michael_Crowley]

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.



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

[Michael_Crowley]

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.

[RCorbeil]

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.