Special join or union?

[Razor]

Hi,

I need to write a query C that will work off tables A & B:

A:
Code Count1
001 2
002 3
003 9

B:
Code Count2
001 5
002 6
004 7

C:
Code Count1 Count2
001 2 5
002 3 6
003 9 0
004 0 7

What join/union would I use to do this? It joins when it
has a value in common, else it takes the value for one
count and puts in a zero if it doesn't exist.

Thanks,
Razor

 

[Tom Ellison]

Dear Razor:

The results shown make it appear you want a "Full Outer Join". While
this is a fairly standard piece of SQL it is not directly supported by
Jet (assuming that's what you're using) but it is easily produced
anyway.

A full outer join can be produced with a UNION of both a LEFT JOIN and
a RIGHT JOIN.

In addition, you seem to require "missing" values, normally resulting
in NULLs to be represented by zero. I'll include that:

SELECT A.Code, A.Count1, Nz(B.Count2, 0) AS Count2
FROM TableA A
LEFT JOIN TableB B ON B.Code = A.Code
UNION
SELECT B.Code, Nz(A.Count1, 0) AS Count1, B.Count2
FROM TableB B
LEFT JOIN TableA A ON A.Code = B.Code

You'll notice I appear to contradict myself, using 2 LEFT JOINs
instead of a LEFT JOIN and a RIGHT JOIN as advertised. However, I
reversed the order of the tables in the FROM clause, which is the same
thing. It just seems to me it reads better. I almost always arrange
queries to use only LEFT JOINs just as a matter of personal style.

Using a UNION eliminated duplicates, but this isn't really as
efficient (at least that's my guess, you can test it) as eliminating
the duplicates inside the second query, like this:

SELECT A.Code, A.Count1, Nz(B.Count2, 0) AS Count2
FROM TableA A
LEFT JOIN TableB B ON B.Code = A.Code
UNION ALL
SELECT B.Code, Nz(A.Count1, 0) AS Count1, B.Count2
FROM TableB B
LEFT JOIN TableA A ON A.Code = B.Code
WHERE A.Code IS NULL

The duplicates of which I speak are those rows with a common Code
value in both tables. Without something to eliminate those duplicates
they will come up twice.

Finally, you probably want them ordered:

SELECT A.Code, A.Count1, Nz(B.Count2, 0) AS Count2
FROM TableA A
LEFT JOIN TableB B ON B.Code = A.Code
UNION ALL
SELECT B.Code, Nz(A.Count1, 0) AS Count1, B.Count2
FROM TableB B
LEFT JOIN TableA A ON A.Code = B.Code
WHERE A.Code IS NULL
ORDER BY Code

Please let me know if this helped, and if I can be of any other
assistance.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Razor]

Thanks Tom, that really helped!
Could you list what other standard SQL is not supported by
Jet (other than Full Outer Joins).
Thanks again,
Razor

 

[Tom Ellison]

Dear Razor:

If, by "Standard SQL" you mean everything in the ANSI specification,
there is no engine in existence that comes anywhere close to
implementing all of it. So, every engine you might learn has a lot of
things not supported.

As a practical issue, there are a few common issues about which people
often ask:

- CASE/WHEN/THEN/ELSE/END is not supported in Jet, but IIf usually
suffices.

- Jet does not usually support subquery references above one level
(cannot reference outside a subquery within a subquery)

- FULL OUTER JOINS

Perhaps others would like to add some they have experienced.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts