Trying to build query to compare 4 tables

[Pete]

I am working with a database with 4 tables. tblPCName, tblPatch1, tblPatch2, tblExcluded. I already know how to exclude pcnames in tblExcluded. What I am trying write a query to find the pcname from the tblPCName that matches the pcname from tblPatch1 or tblPatch2. I don’t know how to do this. I keep getting just the names that are in both tblPatch1 and tblPatch2. All of the tables have the common field pcname.

 

[John Vinson]

What I am trying write a query to find the pcname from the tblPCName that matches the pcname from tblPatch1 or tblPatch2. I don’t know how to do this. I keep getting just the names that are in both tblPatch1 and tblPatch2.

If you want to get records which have pcname in *either* tblPatch1
*or* tblPatch2, you'll need a Subquery. Try;

SELECT * FROM tblPCName
WHERE
(EXISTS (SELECT pcname FROM tblPatch1 WHERE tblPatch1.pcname =
tblPCName.pcname)
OR
EXISTS (SELECT pcname FROM tblPatch2 WHERE tblPatch2.pcname =
tblPCName.pcname))
AND NOT EXISTS
(SELECT pcname FROM tblExclude WHERE tblExclude.pcname =
tblPCName.pcname);

 

[John Nurick]

Hi Pete,

One way is to union two queries, one getting records that match those in
one table, and the other those that match the second table:

SELECT tblPCName.* FROM tblPCName INNER JOIN tblPatch1
ON tblPCName.pcname = tblPatch1.pcname
UNION
SELECT tblPCName.* FROM tblPCName INNER JOIN tblPatch2
ON tblPCName.pcname = tblPatch2.pcname

I am working with a database with 4 tables. tblPCName, tblPatch1,

tblPatch2, tblExcluded. I already know how to exclude pcnames in
tblExcluded. What I am trying write a query to find the pcname from the
tblPCName that matches the pcname from tblPatch1 or tblPatch2. I don’t
know how to do this. I keep getting just the names that are in both
tblPatch1 and tblPatch2. All of the tables have the common field pcname.

 

[Pete]

These work great. Now how do I cleanup the data so that I don't have a bunch of duplicate pcnames. That might appear in tblpatch1 and tblpatch2. All I need to know are the pcs that need to be patched, not what patch they don't have. At least not for this report.
Thanks for the help.

----- John Nurick wrote: -----

Hi Pete,

One way is to union two queries, one getting records that match those in
one table, and the other those that match the second table:

SELECT tblPCName.* FROM tblPCName INNER JOIN tblPatch1
ON tblPCName.pcname = tblPatch1.pcname
UNION
SELECT tblPCName.* FROM tblPCName INNER JOIN tblPatch2
ON tblPCName.pcname = tblPatch2.pcname

I am working with a database with 4 tables. tblPCName, tblPatch1,

tblPatch2, tblExcluded. I already know how to exclude pcnames in
tblExcluded. What I am trying write a query to find the pcname from the
tblPCName that matches the pcname from tblPatch1 or tblPatch2. I don’t
know how to do this. I keep getting just the names that are in both
tblPatch1 and tblPatch2. All of the tables have the common field pcname.