Report using asset and linked registry table

[dabravokid]

Hi, thanks for reading...
I have a report that I need to build and due to a non-standard environment I'm struggling a little. The workstations we use are not on AD and as such login with a default user name, we use Novell and this username is stored in the volatile environment under current user in registry. I have added this as a reg key to scan and can see this data on machines that have been scanned. I have a report that shows all assets with a version of project installed and this displays fine. When I add the registry table to the report it then shows all 3 lines for each asset so I get multiple entries in the report for each asset. How can I build the report to show a single asset with the registry key if it finds it but still shows a line if it doesn't find the key.

Sql without the reg table as follows:

Select Distinct Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.IPAddress,
tblAssets.Username,
tblAssets.Userdomain,
tblADusers.Firstname,
tblADusers.Lastname,
tblSoftwareUni.softwareName,
tblSoftware.softwareVersion,
tblSoftwareUni.SoftwarePublisher,
tblAssets.Lastseen
From tblAssets
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Left Join tblADusers On tblADusers.Username = tblAssets.Username And
tblADusers.Userdomain = tblAssets.Userdomain
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblSoftwareUni.softwareName Like '%microsoft project%' And
tblAssets.Lastseen > GetDate() - 30
Order By tblAssets.Domain,
tblAssets.AssetName,
tblSoftwareUni.softwareName,
tblSoftware.softwareVersion

Sql with extra table that only shows the lines if it have the correct registry item:

Select Distinct Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.IPAddress,
tblAssets.Username,
tblAssets.Userdomain,
tblADusers.Firstname,
tblADusers.Lastname,
tblSoftwareUni.softwareName,
tblSoftware.softwareVersion,
tblSoftwareUni.SoftwarePublisher,
tblAssets.Lastseen,
tblRegistry.Value,
tblRegistry.Valuename
From tblAssets
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Left Join tblADusers On tblADusers.Username = tblAssets.Username And
tblADusers.Userdomain = tblAssets.Userdomain
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Inner Join tblRegistry On tblAssets.AssetID = tblRegistry.AssetID
Where tblSoftwareUni.softwareName Like '%microsoft project%' And
tblAssets.Lastseen > GetDate() - 30 And tblRegistry.Valuename = 'NWUSERNAME'
Order By tblAssets.Domain,
tblAssets.AssetName,
tblSoftwareUni.softwareName,
tblSoftware.softwareVersion

What I actually want is to show this entry if it finds it but also displays a line for the others if they don't have this key...

[Susan_A · Accepted solution]

FYI for everyone: you can use a sub-query for this. I've posted a sample report below.

Select Distinct Top 1000000 tsysOS.Image As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.IPAddress,
  tblAssets.Username,
  tblAssets.Userdomain,
  tblADusers.Firstname,
  tblADusers.Lastname,
  tblSoftwareUni.softwareName,
  tblSoftware.softwareVersion,
  tblSoftwareUni.SoftwarePublisher,
  tblAssets.Lastseen,
  SubQuery.Value,
  SubQuery.Valuename
From tblAssets
  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Left Join tblADusers On tblADusers.Username = tblAssets.Username And
    tblADusers.Userdomain = tblAssets.Userdomain
  Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
  Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
  Left Join (Select Distinct Top 1000000 tblRegistry.AssetID,
    tblRegistry.Value,
    tblRegistry.Valuename
  From tblRegistry
  Where tblRegistry.Valuename = 'NWUSERNAME') SubQuery On SubQuery.AssetID =
    tblAssets.AssetID
Where tblSoftwareUni.softwareName Like '%microsoft project%' And
  tblAssets.Lastseen > GetDate() - 30
Order By tblAssets.Domain,
  tblAssets.AssetName,
  tblSoftwareUni.softwareName,
  tblSoftware.softwareVersion