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 <> 0This query works great and returns what I thought was the right result set. The results that I get are anything that is not equal to a zero stock in table1 and all items from table2 that are not in table1. The problem is if an item has 0 quantity in table1 and a non-zero quantity in table2 it will not display. I tried adding the following the first portion of the query (before the union):
OR B.qtyStock <> 0
This will eventually work but takes forever and a day. Plus is returns several repetitive records. So, this is not an effective solution. Any suggestions on how to show the items which are non-zero in table2 but are zero in table1 would be greatly appreciated.
Thanks for your help in advance!
-JSLucido
