Left and Right Joins

[JoeA2006]

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?

 

[Ken Sheridan]

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

 

[Douglas J. Steele]

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