Help With a query

[Ciar?n]

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?

Ciarán

[Hugo Kornelis]

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

Ciarán

Hi Ciarán

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)

[Ciar?n]

>

Hi Ciarán

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.

Ciarán

[Hugo Kornelis]

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 Ciarán,

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)