specific software and autostart entry

[pskup]

Hello everyone,
I tried to create a report showing all pc with a specific software installed. In addition, I want to know if a specific tool (on these machines) is started via autostart.

I tried to exclude all unwanted entrys in autostart via the where clause "tblAutorunUni.Caption Like N'MyAutostartTool'". But then the pc without these entrys weren't shown, of course.

I tried "left join", "not exist", "not in", "case else" but I can not achieve the desired result.

So i need your help

I have reduced the query to the required entries:

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetUnique,
  tblSoftwareUni.softwareName As software,
  tblAutorunUni.Caption
From tblSoftware
  Inner Join tblAssets On tblSoftware.AssetID = tblAssets.AssetID
  Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
  Inner Join tblAutorun On tblAssets.AssetID = tblAutorun.AssetID
  Inner Join tblAutorunUni On tblAutorunUni.AutorunUNI = tblAutorun.AutorunUNI
Where tblSoftwareUni.softwareName Like 'MySoftware' And
  tblAutorunUni.Caption Like N'MyAutostartTool'

I get this table:

PC1 / MySoftware / MyAutostartTool
PC3 / MySoftware / MyAutostartTool

But I want this:

PC1 / MySoftware / MyAutostartTool
PC2 / MySoftware /
PC3 / MySoftware / MyAutostartTool

[Daniel_B · Accepted solution]

You can do this with a CASE statement and a subquery. Otherwise your filter will only show the computers having the software in the autostart.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetUnique,
  tblSoftwareUni.softwareName As software,
  Case When tblAssets.AssetID In (Select tblAutorun.AssetID
    From tblAutorun Inner Join tblAutorunUni On tblAutorunUni.AutorunUNI =
        tblAutorun.AutorunUNI
    Where tblAutorunUni.Caption Like 'MySoftware%') Then 'yes' Else 'no'
  End As [autorun MySoftware]
From tblAssets
  Inner Join tblSoftware On tblSoftware.AssetID = tblAssets.AssetID
  Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
Where tblSoftwareUni.softwareName Like 'MySoftware%'