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.softwareNameMy 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