Alright, I am hitting my head against the wall here. I have a SQL statement which works pretty well but it seems to be missing one important piece of data. Let me first show you the code:

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

This 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

Reply via email to