Please help with a query

[Tim]

I am trying to query my SQL 2000 SP3 database for some info but can't
seem to figure out how to get what I want. I have a fairly simple
situation.... the data I want is in two tables, one with a single row
for every computer in my domain and another with multiple rows for each
computer that shows software installed on the computer. It looks
something like this:

Computers Table
---------------
CompName OS BIOS etc.
-------- -- ----
Comp1 XP XYZ
Comp2 XP XYZ
Software Table
--------------
CompName Software Package
-------- ----------------
Comp1 Adobe Acrobat Reader
Comp1 Microsoft Office
Comp1 Winzip
Comp2 Adobe Acrobat Reader
Comp2 Microsoft Visio
Comp2 Winzip

What I want to do is to figure out what computers from the computers
table DO NOT have a specific piece of software installed. I have
figured out how to find out which ones have it, but not the reverse.

Sorry if this is a simple one, but I am fairly new to SQL and haven't
figure out the different types of joins and union statements yet.

[Stu]

You can do this with a subquery, using the NOT EXISTS option

SELECT ColList
FROM Computers
WHERE CompName NOT EXISTS (SELECT CompName
FROM Software
WHERE SoftwarePackage = 'WinZip')

The subquery first pulls back all of the computers which do have Winzip
on them, and the outer query shows the list of computers that are not
in the subquery.

Stu

[Tim]

Thanks Stu,

I never could get your suggestion to work. I always got an empty
result set, even if there were computers with no corresponding software
row. But you led me down the right path. After searching Books
Online, I was able to make this work:

SELECT computer_name, computer_invent ory_date
FROM dbo.computers
WHERE (computer_name NOT IN
(SELECT computer_name
FROM software
WHERE software_name = 'Winzip'))

Thanks a lot.

Tim