Left and Right Joins

J

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?
 
K

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
 
D

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top