need SQL help with report showing various software

[amerisbank]

I have written a report to show our McAfee AntiVirus software modules and versions.
I am having problems with the SQL coding. I want to show all computers that have any of the 4 softwares listed. My report currently shows only computers that have all 4 installed. Changing the "AND" to "OR" breaks the report and shows a list of any software.

Do I need to do 4 different SELECT FROM statements instead maybe?

use lansweeperdb
select co1.ComputerName,
sfw1.SoftwareName, sfw1.SoftwareVersion,
sfw2.SoftwareName, sfw2.SoftwareVersion,
sfw3.SoftwareName, sfw3.SoftwareVersion,
sfw4.SoftwareName, sfw4.SoftwareVersion
from tblComputers co1
left outer join tblSoftware sfw1 on (co1.ComputerName = sfw1.ComputerName)
left outer join tblSoftware sfw2 on (co1.ComputerName = sfw2.ComputerName)
left outer join tblSoftware sfw3 on (co1.ComputerName = sfw3.ComputerName)
left outer join tblSoftware sfw4 on (co1.ComputerName = sfw4.ComputerName)

where
co1.ComputerName like '%'
and
(
(sfw1.SoftwarePublisher like '%McAfee%' and sfw1.SoftwareName like '%VirusScan%')
or
(sfw2.SoftwarePublisher like '%McAfee%' and sfw2.SoftwareName like '%AntiSpyware%')
or
(sfw3.SoftwarePublisher like '%McAfee%' and sfw3.SoftwareName like '%SiteAdvisor%')
or
(sfw4.SoftwarePublisher like '%McAfee%' and sfw4.SoftwareName = 'McAfee Agent')
)
order by co1.ComputerName

Thanks in advance,
Claud

[Hemoco]

(might be a better way, not tested)

You will need to do 4 different select commands like this.

Select tblComputers.Computername, tblSoftware.softwareName
From tblComputers Inner Join
tblSoftware On tblComputers.Computername = tblSoftware.ComputerName
Where tblSoftware.softwareName = 'VirusScan Enterprise'

Then link all of them together to table tblcomputers.

[amerisbank]

yes, along with the software name and version.. similar to this.. (see attached). Thanks, Claud

Computer sfw1.Name sfw1.Version sfw2.Name sfw2.Version sfw3.Name sfw3.Version sfw4.Name sfw4.Version
comp1 VirusScan Enterprise 8.5.0 AntiSpyware 2.4.1 SiteAdvisor 1.2.5 McAfee Agent 2.01
comp2 VirusScan Enterprise 8.7.1 (blank spot) (blank spot) SiteAdvisor 1.2.7 McAfee Agent 2.01
comp3 VirusScan Enterprise 8.5.0 AntiSpyware 2.4.1 SiteAdvisor 1.2.5 McAfee Agent 2.01
comp4 (blank spot) (blank spot) (blank spot) (blank spot) (blank spot) (blank spot) (blank spot) (blank spot)
comp5 VirusScan Enterprise 8.5.0 AntiSpyware 2.4.1 SiteAdvisor 1.2.5 McAfee Agent 2.01
comp6 VirusScan Enterprise 8.5.0 AntiSpyware 2.4.1 (blank spot) (blank spot) McAfee Agent 2.01
comp7 VirusScan Enterprise 8.5.0 AntiSpyware 2.4.1 SiteAdvisor 1.2.5 McAfee Agent 2.01

[Hemoco]

So if I understand correct, if any of these show up in the software list then the computername should be included in the report.

If 2 of these softwares appear, do you want to show 2 computers?