Left and Right Joins

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

JoeA2006 - 31 May 2006 23:00 GMT

I am trying to create a query that will show values from two tables
regardless of which table supplies a value and which supplies the null. The
two tables have a common fields- Category and Amount. The Category values
should match a corresponding record in the other table if one exists. The
dollar amount does not always match. The result of the query should look like
this

Category AmountA AmountB
A $$$.$$ $$$.$$
B $$$.$$
C $$$.$$
D $$$.$$ $$$.$$

Is it possible to do a Left and Right join in the same query or is this be
performed another way?

Reply to this Message

Ken Sheridan - 31 May 2006 23:24 GMT

You can do it with a UNION operation on two OUTER JOINs:

SELECT Table1.Category, Table1.Amount AS AmountA, Table2.Amount AS AmountB
FROM Table1 LEFT JOIN Table 2
ON Table1.Category = Table2.Category
UNION
SELECT Table2.Category, Table1.Amount, Table2.Amount
FROM Table1 RIGHT JOIN Table 2
ON Table1.Category = Table2.Category
ORDER BY Category;

Ken Sheridan
Stafford, England

> I am trying to create a query that will show values from two tables
> regardless of which table supplies a value and which supplies the null. The
[quoted text clipped - 11 lines]
> Is it possible to do a Left and Right join in the same query or is this be
> performed another way?

Reply to this Message

Douglas J. Steele - 31 May 2006 23:29 GMT

You'll need to do two queries: one with a Left and the other with a Right,
and UNION them together.

SELECT A.Category, A.Amount AS AmountA, B.Amount As AmountB
FROM Table1 AS A RIGHT JOIN Table2 AS B
ON A.Category = B.Category
UNION
B.Category, A.Amount AS AmountA, B.Amount As AmountB
FROM Table1 AS A LEFT JOIN Table2 AS B
ON A.Category = B.Category

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

>I am trying to create a query that will show values from two tables
> regardless of which table supplies a value and which supplies the null.
[quoted text clipped - 13 lines]
> Is it possible to do a Left and Right join in the same query or is this be
> performed another way?

Reply to this Message