License report - show all software

[joes3]

I'm in need of some SQL assistance. I'm trying to design a report that lists every computer that has Adobe Acrobat installed on it, which version of Acrobat is installed, and the Product Key for Acrobat if it exists. It's the last bit that's throwing me for a loop. Seems like tblSoftware and tblSoftwareUni don't share a direct relationship with tblSerialnumber. I can't even match it based on the name of the software since tblSerialnumber.Product and tblSoftwareUni.softwareName aren't identical. This means that when I try to join those tables to tblAssets, I get... wrong information. Presumably it's an issue with my JOINS (not sure I'm using the right ones) so if someone else could help me with this I'd appreciate it.

Here's what I have so far:

Select Top 1000000 tblAssets.AssetName,
  soft.softwareName,
  soft.softwareVersion,
  soft.SoftwarePublisher,
  serial.ProductKey
From tblAssets
  Join (
    Select tblSoftware.AssetID,
      tblSoftwareUni.softwareName,
      tblSoftware.softwareVersion,
      tblSoftwareUni.SoftwarePublisher
    From tblSoftware
      Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID)
    As soft On soft.AssetID = tblAssets.AssetID
  Join (
    Select tblSerialnumber.AssetID,
      tblSerialnumber.ProductKey
    From tblSerialnumber)
	As serial On serial.AssetID = tblAssets.AssetID
Order By tblAssets.AssetName

I would like it to look like this where it shows every computer with Acrobat and lists the product key when it's available but still lists the computer and the Acrobat version info for those computers where the product key isn't available.



Of course, a better solution would be if Lansweeper could gather the Product Key from every version of Acrobat *hint hint* because then I could just run the built-in License: Software licensekey overview report and get everything I need. I'm currently on the trial version of Lansweeper which is listed as 5.1.0.55 on the license page.

[Hemoco · Accepted solution]

It is impossible to make a report that links the table tblsoftware and tblserialnumber. So the report that you want to make is currently impossible.

We will take your remarks in mind but I cannot assure you that this is going to be implemented.