Help With a query

If I have table1 and table2, which both have the same fields Product
and Qty. Can you help me with this query...

table1 has two rows
Row 1 Contains ProductA with a Qty of two
Row 2 Contains ProductB with a Qty of four

table2 has has two rows
Row 1 Contains ProductB with a Qty of two
Row 2 Contains ProductC with a Qty of three

What I want to return from a SQL query is a table with three fields...
Product, Table1QTY and Table2QTY
With the example above I would like the result to have three rows, one
for each product.

Any help?

CiarnOn 9 Nov 2004 03:48:54 -0800, Ciar?n wrote:

>If I have table1 and table2, which both have the same fields Product
>and Qty. Can you help me with this query...
>
>table1 has two rows
>Row 1 Contains ProductA with a Qty of two
>Row 2 Contains ProductB with a Qty of four
>table2 has has two rows
>Row 1 Contains ProductB with a Qty of two
>Row 2 Contains ProductC with a Qty of three
>
>What I want to return from a SQL query is a table with three fields...
>Product, Table1QTY and Table2QTY
>With the example above I would like the result to have three rows, one
>for each product.
>Any help?
>Ciarn

Hi Ciarn

Try if this works for you:

SELECT COALESCE(a.Product, b.Product) AS Product,
COALESCE(a.Qty,0) + COAELSCE(b.Qty,0) AS Qty
FROM table1 AS a
FULL OUTER JOIN table2 AS b
ON a.Product = b.Product

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||>
> Hi Ciarn
> Try if this works for you:
> SELECT COALESCE(a.Product, b.Product) AS Product,
> COALESCE(a.Qty,0) + COAELSCE(b.Qty,0) AS Qty
> FROM table1 AS a
> FULL OUTER JOIN table2 AS b
> ON a.Product = b.Product
> Best, Hugo

Hugo,

Thanks a million.
Had to tweak the script ever so slightly to get the Qtys in seperate
columns like below, but wouldn't have figured it out without your
help.

SELECT COALESCE (A.Product, B.Product) AS PRODUCT, COALESCE
(A.Qty, 0) AS QTY_A, COALESCE (B.Qty, 0) AS QTY_B
FROM Sheet1 A FULL OUTER JOIN
Sheet2 B ON A.Product = B.Product
WHERE A.Qty>B.Qty

Cheers again.

Ciarn|||On 9 Nov 2004 07:19:49 -0800, Ciar?n wrote:

>Had to tweak the script ever so slightly to get the Qtys in seperate
>columns like below, but wouldn't have figured it out without your
>help.

Hi Ciarn,

I see I misread your query - somehow, I thought you wanted the total.
Apologies for that.

>SELECT COALESCE (A.Product, B.Product) AS PRODUCT, COALESCE
>(A.Qty, 0) AS QTY_A, COALESCE (B.Qty, 0) AS QTY_B
>FROM Sheet1 A FULL OUTER JOIN
> Sheet2 B ON A.Product = B.Product
>WHERE A.Qty>B.Qty

Are you sure about the WHERE clause? With default ANSI settings, this
would remove the rows for all products that are pressent in only Sheet1 or
in only Sheet2, since a comparison involving NULL will never evaluate to
TRUE.

Since this requirement is far from obvious from your first post, I wonder
why you included this WHERE clause and what the acutal business problem
you have to solve is.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)