Exclusion-Type Join

  • Thread starter Thread starter Gummball
  • Start date Start date
G

Gummball

Hello!

I need some help with a query I'm trying to write. I have two tables with
customer information, tblA and tblB with unique identifiers tblA.id and
tblB.id. I'm trying to write a query to return results of everyone in tblA
who is NOT in tblB and vice versa. Here is an example:

tblA
----------
| ID |
----------
| 1 |
| 2 |
| 5 |
----------

tblB
----------
| ID |
----------
| 2 |
| 3 |
| 5 |
----------

Since 2 and 5 appear in both tables, 1 in tblA only, and 3 in tblB only, the
query should return:

qryAB
-------------------
| ID | tbl |
-------------------
| 1 | A |
| 3 | B |
-------------------

I've written a big jumbled mess of unions and joins aggregating all possible
values of ID then joining each of A and B, resulting in this:

----------------------------
| ID | tblA | tblB |
----------------------------
| 1 | A | |
| 2 | A | B |
| 3 | | B |
| 5 | A | B |
----------------------------

Then I took all values of ID where either tblA or tblB is null.

This gets me to the data set I need, but I'm wondering if there's an easier
way to do it?

Thanks for any help!
 
Gummball wrote:
<snip>

You didn't provide your resulting SQL so I don't know if it's similar to
what I have but this should give you a single column of id not in either
tables.


SELECT u.ID
FROM (SELECT a.ID
FROM tbla a
UNION
SELECT b.ID
FROM tblb b
) u
LEFT JOIN tbla a
ON a.id = u.id
LEFT JOIN tblb b
ON b.id = u.id
WHERE
a.id IS NULL OR
b.id IS NULL;
 
Thanks! This is similar to the SQL I wrote. I didn't provide it b/c the SQL
for the actual data is much more complicated (there are many more tables,
different keys depending on the table join, etc.).

I was wondering if there was some other way to do it. A left join returns
all results from tblA and all those from tblB with a matching value, so I was
wondering if there was maybe another type of join that would return ONLY
those in tblA that DIDN'T have a matching value in tblB. I'm guessing
there's not =(

Thanks!
 
I would think something like the following should work.

SELECT tblA.ID, "A" as SourceTable
FROM TblA LEFT JOIN TblB
ON tblA.ID = TblB.ID
WHERE TblB.ID is Null
UNION ALL
SELECT tblB.ID, "B" as SourceTable
FROM TblA RIGHT JOIN TblB
ON tblA.ID = TblB.ID
WHERE TblA.ID is Null


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Back
Top