Jeff,
> SELECT A.item, A.qtyStock, B.item, B.qtyStock
> FROM table1 A, table2 B
> WHERE A.item = B.item
> AND A.qtyStock <> 0
>
> UNION
>
> SELECT 0, 0, item, qtyStock
> FROM table2
> WHERE item NOT IN
> (SELECT item
> FROM table1)
> AND qtyStock <> 0
>
> The problem is if an item has 0 quantity in
> table1 and a non-zero quantity in table2 it
> will not display.
The second half of the union is only adding items from table2 if they do
not appear in table1 at all. What you want is all the items from table2
that did not appear in the first half of the union. You can do this by
adding a "WHERE qtyStock <> 0" clause to your nested subquery in the second
half of the union:
SELECT
table1.item AS item1,
table1.qtyStock AS qtyStock1,
table2.item AS item2,
table2.qtyStock AS qtyStock2
FROM table1,table2
WHERE (A.item = B.item)
AND (A.qtyStock <> 0)
UNION
SELECT
0 AS item1,
0 AS qtyStock1,
item AS item2,
qtyStock AS qtyStock2
FROM table2
WHERE (item NOT IN (
SELECT item
FROM table1
WHERE qtyStock <> 0
))
AND (qtyStock <> 0)
Note that if an item is in table1 with a non-zero quantity, but not in
table2, this query will not display it. My guess is that you actually want
three different types of records: records with a non-zero quantity in
table1, records with a non-zero quantity in table2, and records with
non-zero quantities in both tables. You could do this by adding another
union to your existing query, but, assuming you are using an ANSII
compatible database, you really would be better off using a full outer join:
SELECT
NVL(table1.item,0) AS item1,
NVL(table1.qtyStock,0) AS qtyStock1,
NVL(table2.item,0) AS item2,
NVL(table2.qtyStock,0) AS qtyStock2
FROM table1
FULL OUTER JOIN table2
ON (table1.item = table2.item)
WHERE (table1.qtyStock <> 0)
OR (table2.qtyStock <> 0)
The NVL() function simply replaces NULL values with the second parameter
(0).
I hope this helps!
- Warren Brown
-----------------------------------------------
To post, send email to [EMAIL PROTECTED]
To unsubscribe:
Send UNSUBSCRIBE to [EMAIL PROTECTED]
To subscribe / unsubscribe: http://www.dfwcfug.org
