Merging 2 reports

[paulruvalcaba]

I'm still fairly new to SQL. Through lots of trial and error I was able to come up with the following 2 reports. I'm trying to merge them but am having difficulty.


This is the Registry report I want to combine with a Software report:

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tsysOS.OSname,
  tblADusers.Firstname,
  tblADusers.Lastname,
  tblAssets.Lastseen,
  tRegistry1.Value As JAVA_HOME,
  tRegistry2.Value As JAVA_TOOL_OPTIONS,
  tRegistry3.Value As PATH
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Left Join (Select tblAssetUserRelations.AssetID,
      tblAssetUserRelations.Username,
      tblAssetUserRelations.Userdomain
    From tblAssetUserRelations
    Where tblAssetUserRelations.Type = 1) tOwner On tblAssets.AssetID =
      tOwner.AssetID
  Inner Join tblState On tblState.State = tblAssetCustom.State
  Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
  Left Join (Select tblRegistry.AssetID,
      tblRegistry.Value
    From tblRegistry
    Where
      tblRegistry.Regkey Like
      'HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\Environment' And tblRegistry.Valuename = 'JAVA_HOME') tRegistry1 On tblAssets.AssetID = tRegistry1.AssetID
  Left Join (Select tblRegistry.AssetID,
      tblRegistry.Value
    From tblRegistry
    Where
      tblRegistry.Regkey Like
      'HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\Environment' And tblRegistry.Valuename = 'JAVA_TOOL_OPTIONS') tRegistry2 On tblAssets.AssetID = tRegistry2.AssetID
  Left Join (Select tblRegistry.AssetID,
      tblRegistry.Value
    From tblRegistry
    Where
      tblRegistry.Regkey Like
      'HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\Environment' And tblRegistry.Valuename = 'PATH') tRegistry3 On tblAssets.AssetID = tRegistry3.AssetID
  Inner Join lansweeperdb.dbo.tsysOS On tsysOS.OScode = tblAssets.OScode
  Inner Join lansweeperdb.dbo.tblADusers On tblADusers.Username =
      tblAssets.Username
Where tsysAssetTypes.AssetTypename = 'Windows' And tblAssetCustom.State = 1

This is the software report that I want to merge into the report above:
NOTE: I do not need the case that adds color to be included if its not possible, but it would be nice.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Username,
  tblADusers.Firstname,
  tblADusers.Lastname,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tsysIPLocations.IPLocation,
  tsysOS.OSname As OS,
  Case
    When tblSoftware.softwareVersion >= '8.0.1810.13' Then 'Up to date'
    Else 'Out of date'
  End As [Patch Status],
  tblSoftware.softwareVersion As Version,
  tblSoftwareUni.softwareName As Software,
  tblSoftwareUni.SoftwarePublisher As Publisher,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  Case
    When tblSoftware.softwareVersion < '8.0.1810.13' Then '#ffadad'
    Else '#d4f4be'
  End As backgroundcolor
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tsysIPLocations On tsysIPLocations.LocationID =
      tblAssets.LocationID
  Inner Join tblState On tblState.State = tblAssetCustom.State
  Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
  Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
  Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Inner Join tblADusers On tblADusers.Username = tblAssets.Username
Where tblSoftwareUni.softwareName Like '%Java%' And tblState.Statename =
  'Active'
Order By Version

Any and all tips and help would be appreciated.

[paulruvalcaba]

SO I was able to SOMEWHAT figure it out. But I am having issues with a few duplicate workstations. I am not sure how to merge/join them

This was what I came up with:

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Username,
  tblADusers.Firstname,
  tblADusers.Lastname,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tsysIPLocations.IPLocation,
  tsysOS.OSname As OS,
  Case
    When tblSoftware.softwareVersion >= '8.0.1810.13' Then 'Up to date'
    Else 'Out of date'
  End As [Patch Status],
  tblSoftware.softwareVersion As Version,
  tblSoftwareUni.softwareName As Software,
  tblSoftwareUni.SoftwarePublisher As Publisher,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tRegistry1.Value As JAVA_HOME,
  tRegistry2.Value As JAVA_HOME_User,
  tRegistry3.Value As JAVA_TOOL_OPTIONS,
  tRegistry4.Value As PATH

From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
    Inner Join tsysIPLocations On tsysIPLocations.LocationID =
      tblAssets.LocationID
  Left Join (Select tblAssetUserRelations.AssetID,
      tblAssetUserRelations.Username,
      tblAssetUserRelations.Userdomain
  From tblAssetUserRelations
    Where tblAssetUserRelations.Type = 1) tOwner On tblAssets.AssetID =
      tOwner.AssetID
	  Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
  Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
  Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Inner Join tblADusers On tblADusers.Username = tblAssets.Username	  
   
  Inner Join tblState On tblState.State = tblAssetCustom.State
  Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
  Left Join (Select tblRegistry.AssetID,
      tblRegistry.Value
    From tblRegistry
    Where
      tblRegistry.Regkey Like
      'HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\Environment' And tblRegistry.Valuename = 'JAVA_HOME') tRegistry1 On tblAssets.AssetID = tRegistry1.AssetID
  Left Join (Select tblRegistry.AssetID,
      tblRegistry.Value
    From tblRegistry
    Where tblRegistry.Regkey Like 'HKEY_CURRENT_USER\Environment' And
      tblRegistry.Valuename = 'JAVA_HOME') tRegistry2 On tblAssets.AssetID =
      tRegistry2.AssetID
  Left Join (Select tblRegistry.AssetID,
      tblRegistry.Value
    From tblRegistry
    Where
      tblRegistry.Regkey Like
      'HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\Environment' And tblRegistry.Valuename = 'JAVA_TOOL_OPTIONS') tRegistry3 On tblAssets.AssetID = tRegistry3.AssetID
  Left Join (Select tblRegistry.AssetID,
      tblRegistry.Value
    From tblRegistry
    Where
      tblRegistry.Regkey Like
      'HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\Environment' And tblRegistry.Valuename = 'PATH') tRegistry4 On tblAssets.AssetID = tRegistry4.AssetID
  Inner Join lansweeperdb.dbo.tsysOS On tsysOS.OScode = tblAssets.OScode
  Inner Join lansweeperdb.dbo.tblADusers On tblADusers.Username =
      tblAssets.Username
Where tsysAssetTypes.AssetTypename = 'Windows' And tblAssetCustom.State = 1 and tblSoftwareUni.softwareName Like '%Java%' And tblState.Statename =
  'Active'
Order By Version

I took these 2 lines out from the bottom because I was getting an error and didnt know how to fix it.

 Inner Join lansweeperdb.dbo.tsysOS On tsysOS.OScode = tblAssets.OScode
  Inner Join lansweeperdb.dbo.tblADusers On tblADusers.Username =
      tblAssets.Username

[size=6]The objects "lansweeperdb.dbo.tsysOS" and "tsysOS" in the FROM clause have the same exposed names. Use correlation names to distinguish them.[/size]