Using subqueries?

[laurin1]

I am trying to get all of the machines that are not at the highest version of Windows 10. I get get the highest version:

Select Max(tblOperatingsystem.Version)
From tblAssets
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Where tblOperatingsystem.Caption Like '%Windows 10%'

And I get all machines < a specific version:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblOperatingsystem.Caption,
tblOperatingsystem.Version,
tblAssets.Lastseen
From tblAssets
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Where tblOperatingsystem.Caption Like '%Windows 10%' And
tblOperatingsystem.Version < '10.0.14393'
Order By tblOperatingsystem.Version

This works in MySQL, but apparently not in (SQLlite?):

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblOperatingsystem.Caption,
tblOperatingsystem.Version,
tblAssets.Lastseen
From tblAssets
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Where tblOperatingsystem.Caption Like '%Windows 10%' And
tblOperatingsystem.Version < (Select Max(tblOperatingsystem.Version)
From tblAssets Inner Join tblOperatingsystem On tblAssets.AssetID =
tblOperatingsystem.AssetID
Where tblOperatingsystem.Caption Like '%Windows 10%')
Order By tblOperatingsystem.Version

[laurin1 · Accepted solution]

I figured out how to do it using a left join:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblOperatingsystem.Caption,
tblOperatingsystem.Version,
tblAssets.Lastseen
From tblAssets
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Left Join (Select Max(tblOperatingsystem.Version) version
From tblAssets
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Where tblOperatingsystem.Caption Like '%Windows 10%') maxv
On maxv.version = tblOperatingsystem.Version
Where tblOperatingsystem.Caption Like '%Windows 10%' And maxv.version Is Null
Order By tblOperatingsystem.Version