Wanting to remove software comparison

[DontByteMe]

Hello,

I am wanting to remove software comparison on this code as its creating duplicate software and Asset names. It seems it runs the antivirus scan then runs a separate antivirus scan to see its version and other products. I do not need it on my list so im wondering if anyone can help simplify this code and help me remove the extra outputs to make it neat and simple to also be able to read just one asset name unless it has two antivirus such as webroot and norton. Thanks for any help provided im newbie.

Select Top 1000000 tblAssets.AssetName,
  AntiVirus.Software,
  AntiVirus.Enabled,
  AntiVirus.UpToDate,
  AntiVirus.RetrievedFrom,
  tblAssets.Domain,
  tblAssets.Username,
  tblAssets.Userdomain,
  tblAssets.IPAddress,
  Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
  tsysIPLocations.IPLocation,
  tsysOS.OSname As OS,
  tblAssets.SP,
  tblAssets.Lastseen,
  tblAssets.Lasttried
From ((Select SoftwareComparison.AssetID As AssetID,
        SoftwareComparison.Software As Software,
        SoftwareComparison.Version As Version,
        'Software Comparison' As RetrievedFrom,
        '' As Enabled,
        '' As UpToDate
      From (Select tblSoftware.AssetID As AssetID,
              tblSoftwareUni.softwareName As Software,
              tblSoftware.softwareVersion As Version
            From tblSoftware
              Inner Join tblSoftwareUni On tblSoftware.softID =
                tblSoftwareUni.SoftID
              Inner Join tsysantivirus On tblSoftwareUni.softwareName Like
                tsysantivirus.Software) As SoftwareComparison)
      Union
      (Select tblAntivirus.AssetID As AssetID,
        tblAntivirus.DisplayName As Software,
        Null As Version,
        'WMI' As RetrievedFrom,
        Case
          When tblAntivirus.onAccessScanningEnabled = 1 Then 'Yes'
          Else 'No'
        End As Enabled,
        Case
          When tblAntivirus.productUpToDate = 1 Then 'Yes'
          Else 'No'
        End As UpToDate
      From tblAntivirus)) AntiVirus
  Inner Join tblAssetCustom On AntiVirus.AssetID = tblAssetCustom.AssetID
  Inner Join tblAssets On tblAssets.AssetID = AntiVirus.AssetID
  Inner Join tsysIPLocations On
    tblAssets.LocationID = tsysIPLocations.LocationID
  Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Inner Join tsysAssetTypes On tblAssets.Assettype = tsysAssetTypes.AssetType
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
  Inner Join tblDomainroles On tblComputersystem.Domainrole =
    tblDomainroles.Domainrole
  Inner Join tblState On tblAssetCustom.State = tblState.State
Where tblDomainroles.Domainrolename In ('Stand-alone Workstation',
  'Member Workstation') And tblState.Statename = 'Active'
Order By tblAssets.Domain,
  tblAssets.AssetName,
  AntiVirus.Software