Non-Unique SW installations per system

[GMFDE]

Sorry, I had to use that subject line because there is an unanswered post from someone else with the subject line I wanted to use and this forum software is set to prevent duplicate posts. Silly really.

To the point. I have been working on a report to find systems with more than one version of Java. It seemed straight forward but isn't working. The report returns 0 results. However, I know from our SCCM environment that we have several hundred systems that qualify. Mgmt has a penchant for LS reporting and doesn't like that they can't filter on the fly in SCCM as they can with LS.

Below is what I have so far...

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblSoftwareUni.softwareName,
  tblSoftware.softwareVersion,
  tsysIPLocations.IPLocation
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
  Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
  Inner Join tsysIPLocations On tsysIPLocations.LocationID =
    tblAssets.LocationID
Where tblSoftwareUni.softwareName Like '%java 7%' And
  tblSoftwareUni.softwareName Like '%java 8%' And tblAssetCustom.State = 1

I searched finished reports for "Multiple versions of software", "Multiple versions" and finally just "version" and "versions" and plowed through several pages of results for unrelated items. If this has already been answered, my apologies for taking up your time.

My thanks in advance.

GMFDE

[RCorbeil]

The problem you're running into is down to the phrasing of your condition. The contents of your WHERE clause is evaluated for each software name. When you say

Where tblSoftwareUni.softwareName Like '%java 7%' And
  tblSoftwareUni.softwareName Like '%java 8%'

you're saying "if the current record is both Java 7 and Java 8 then...".

Take a look at this thread. Depending on how you want to report the results, this thread might be useful, too.

[GMFDE]

RC62N wrote:
The problem you're running into is down to the phrasing of your condition. The contents of your WHERE clause is evaluated for each software name. When you say

Where tblSoftwareUni.softwareName Like '%java 7%' And
  tblSoftwareUni.softwareName Like '%java 8%'

you're saying "if the current record is both Java 7 and Java 8 then...".

Take a look at this thread. Depending on how you want to report the results, this thread might be useful, too.

That's actually the goal. Java is one of those apps where you can have multiple versions installed on the same machine. In this case, we have about 2100 computers with Java 7 still installed but about half of those also have Java 8 installed as well. My next goal is to uninstall Java 7 from the remaining systems but I need to prioritize those systems that have both. The 'or' statement in this case only evaluates for either Java 7 or Java 8. What I need is in fact what you stated, 'Java 7 AND Java 8'. However, I think I found a way to do this in SCCM reporting. But, for future reference, I would still like to know how to do this.

I did try the code listed in the second post you reference and while it has potential, one of my alternative goals is to create a chart from this data and I don't think I can make that method feed properly for charting.

All of that having been said, thank you for your help.