Problem displaying a column from a different table in Select

[lunesolitaire]

Good morning,

I have inspiration from a solution from dear Daniel_B in this topic  

It works great to display Assets containing "Software A" AND "Software B", in my case,

the only difference is that I used the "OR" logic according to our work, either displaying Assets containing "Software A" OR "Software B" .

there is no problem for that.

But this example can only display these 6 columns:  

AssetName Domain AssetTypename IPAddress Lastseen Lasttried

I need to also display softwareName as one more column.

So as soon as I add 

       tblSoftwareUni.softwareName As Software

in the Select section and add

   Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID

   Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID

in the "From tblAssets" section

Therefore, after this modification ...  

Lansweeper gave me a too long list with assets having not only Software A/B, but also all their other applications installed on Assets that meet the criteria (with two software installed)... I only need to display the records about these two software A/B

Can anyone tell me what my mistake is?

Many thanks in advance!  Here is my code:

Select Top 1000000 tblAssets.AssetID,
    tblAssets.AssetName,
    tblAssets.Domain,
    tsysAssetTypes.AssetTypename,
    tsysAssetTypes.AssetTypeIcon10 As icon,
    tblAssets.IPAddress,
    tblSoftwareUni.softwareName As Software,
    tblAssets.Lastseen,
    tblAssets.Lasttried
From tblAssets
    Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
    Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
    Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
    Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where (tblAssets.AssetID In (Select tblSoftware.AssetID
    From tblSoftware Inner Join tblSoftwareUni On tblSoftware.softID =
        tblSoftwareUni.SoftID
    Where tblSoftwareUni.softwareName Like '%Firefox%') Or
    tblAssets.AssetID In (Select tblSoftware.AssetID
    From tblSoftware Inner Join tblSoftwareUni On tblSoftware.softID =
        tblSoftwareUni.SoftID
    Where tblSoftwareUni.softwareName Like '%Chrome%')) And
    tblAssetCustom.State = 1

Order By tblAssets.IPAddress,
tblAssets.AssetName

[lunesolitaire · Accepted solution]

I finally found the problem by simply stating the required conditions in MAIN-WHERE.  In the original post,  there is no need to perform SUB-SELECT in main-Where....Maybe we've all made things complicated

Here is the code that can filter normally:

Select Top 1000000 tblAssets.AssetID,
    tblAssets.AssetName,
    tblAssets.Domain,
    tsysAssetTypes.AssetTypename,
    tsysAssetTypes.AssetTypeIcon10 As icon,
    tblAssets.IPAddress,
    tblSoftwareUni.softwareName As Software,
    tblAssets.Lastseen,
    tblAssets.Lasttried
From tblAssets
    Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
    Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
    Inner Join tblSoftware On tblSoftware.AssetID = tblAssets.AssetID
    Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where (
          (tblSoftwareUni.softwareName Like '%Software_Name_1%') Or
          (tblSoftwareUni.softwareName Like '%Software_Name_2%')
      ) And
      tblAssetCustom.State = 1
Order By tblAssets.IPAddress,
tblAssets.AssetName