Support for nested queries?

[mc_connected]

I want to list all assets from a hard-coded list and next to each asset, the version of CrashPlan installed, or blank where it is not installed.

Here is my query:

Select 
  tblAssets.Username
  , (select s.softwareVersion
	from tblSoftware s
		inner join tblSoftwareUni su on s.softwareid = su.softwareid
	where su.softwarename like '%crashplan%') as CrashPlanVersion
From tblAssets
Where 
	tblAssets.AssetName In ('ANITA-PC', 'AUDREY-PC','CHRISWARD-PC', 'JEFF-PC-2015', 'JOY-PC', 'JOYCE-PC', 'MARIANN-WILL-PC','RAY-PC', 'ROBERT-PC', 'TIFF-LAPTOP', 'FRANCES-PC', 'CRAIG-I7')

However, I get the following error:

There was an error parsing the query. [ Token line number = 1,Token line offset = 29,Token in error = Select ]

Admittedly, I don't know how to interpret the error, except perhaps I cannot do a nested query as one of the columns?

[mc_connected]

Ah, of course: a subquery in the left join. It works, thank you!

[Susan_A · Accepted solution]

The error you are seeing is a generic error indicating that the query is built incorrectly. You can use subqueries, but the subquery in your report is placed incorrectly. It's not linked to tblAssets either, so the report output would not make sense this way. Below would be a correct query for what you are trying to accomplish. Keep in mind that this is general SQL syntax, not specific to Lansweeper.

Select Top 1000000 tblAssets.Username,
  SubQuery.softwareVersion As CrashPlanVersion
From tblAssets
  Left Join (Select tblSoftware.AssetID,
    tblSoftware.softwareVersion
  From tblSoftware
    Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
  Where tblSoftwareUni.softwareName Like '%crashplan%') SubQuery
    On SubQuery.AssetID = tblAssets.AssetID
Where tblAssets.AssetName In ('ANITA-PC', 'AUDREY-PC', 'CHRISWARD-PC',
  'JEFF-PC-2015', 'JOY-PC', 'JOYCE-PC', 'MARIANN-WILL-PC', 'RAY-PC',
  'ROBERT-PC', 'TIFF-LAPTOP', 'FRANCES-PC', 'CRAIG-I7')