SQL question - compare 2 tables

[Guest]

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

 

[Guest]

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

 

[Jamie Collins]

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)

This query will run very slow. I did a quick test where tblA has 100K
rows and tblB 10K rows. I don't know how long it would've taken to
complete but I killed the process after about ten minutes.

A quick re-write using an OUTER JOIN:

SELECT DT1.a, DT1.a1, DT1.a2, DT1.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
) AS DT1
LEFT JOIN tblB ON DT1.a = tblB.b1
WHERE tblB.b1 IS NULL;

On the same 100K and 10K tables, this version executed in about five
seconds.

Jamie.

--