RE: SQL question - compare 2 tables

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Gerald Stanley (GeraldStanley_at_discussions.microsoft.com)
Date: 11/09/04 

Date: Tue, 9 Nov 2004 14:29:02 -0800

To find which entries are in tblA but not in tblB, try something along the
lines of
SELECT a FROM (
SELECT a1 AS a FROM tblA
UNION
SELECT a2 FROM tblA
UNION
SELECT a3 FROM tblA)
WHERE a NOT IN (SELECT b1 FROM tblB)

The above can be modified should you wish to know the full row in tblA
SELECT a, a1, a2, a3 FROM (
SELECT a1 AS a, a1, a2, a3 FROM tblA
UNION
SELECT a2, a1, a2, a3 FROM tblA
UNION
SELECT a3, a1, a2, a3 FROM tblA)
WHERE a NOT IN (SELECT b1 FROM tblB)

Hope This Helps
Gerald Stanley MCSD

"GreggK" wrote:

> I have 2 tables (tblA abd tblB)
> tblA has 3 fields (a1, a2, and a3). All are text fields.
> tblB has one field (b1). Text field.
> Both are tables listing vaious part numbers for an assembly (a collection of
> parts that belong together)
> tblA contains every part number and is segragated by something called "part
> level" and hence the 3 fields.
> tblB is supposed to contain all the part in tblA in a single field. But some
> of the parts in tblB may be missing.
> I need to find what parts are missing from tblB
> The SQL statement I used was:
> SELECT tblA.a1, tblA.a2, tblA.a3
> FROM tblA
> LEFT JOIN tblB ON
> tblA.a1=tblB.b1
> OR tblA.a2=tblB.b1
> OR tblA.a3=tblB.b1
> WHERE tblB.b1 Is Null;
>
> This gives me expected results only where every part number in a row in tblA
> is not in tblB.
>
> If there is a single part number in tblA that is not in tblB, I do not get
> the results I need (e.g., the part in row 3 for field a2 is not in tblB but
> the parts in fields a1 and a3 for row 3 are in tblB)
>
> Is there a way to compared individual entries in tblA to tblB?
>
>
>
> Expand AllCollapse All

Relevant Pages

  • Re: Update all records in VBA
    ... there's still some unique ID that is consistant in tblA and tblB after the ... are then stored in tblParts. ... doing the import and update tblA and tblB in VBA. ...
    (microsoft.public.access.modulesdaovba)
  • Re: Trigger After Delete - I need a Trigger Before Delete
    ... I have a table tblB which has to be filled whenever in table tblA ... This view vwC is based on a key field used in tblA. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (comp.databases.ms-sqlserver)
  • Strange behavior on query - Help??
    ... I have tblA with unique PrimaryKeyX. ... Then tblB has many PrimaryKeyX matches, and for each there is a fieldY ...
    (microsoft.public.access.queries)
  • Re: Option Group
    ... Yes they have the same feilds and since thay have the ... same feilds on bother tblA anf tblB I have decided to may ... Option Group: ...
    (microsoft.public.access.forms)
  • SQL question - compare 2 tables
    ... I have 2 tables (tblA abd tblB) ... tblB is supposed to contain all the part in tblA in a single field. ... I need to find what parts are missing from tblB ...
    (microsoft.public.access.queries)