Trying to build query to compare 4 tables

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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.
Click to expand...

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);
 
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,
Click to expand...
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.
 
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,
Click to expand...
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.
 
Back
Top