This should be close to what you need.
SELECT
COALESCE(A.item, 0) as [item]
, COALESCE(A.qtyStock, 0) as [qtyStock]
, B.item, B.qtyStock
FROM
table1 A
RIGHT OUTER JOIN
table2 B
ON
A.item = B.item
AND A.qtyStock <> 0
WHERE
B.qtyStock <> 0
David L. Penton, Microsoft MVP
JCPenney Application Specialist / Lead
"Mathematics is music for the mind, and Music is Mathematics for the
Soul. - J.S. Bach"
[EMAIL PROTECTED]
Do you have the VBScript Docs or SQL BOL installed? If not, why not?
VBScript Docs: http://www.davidpenton.com/vbscript
New SP3 SQL BOL: http://www.davidpenton.com/sqlbol
---------- Original Message -----------
From: "Jeff S. Lucido" <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED]
Sent: Wed, 02 Jul 2003 13:12:03 -0500
Subject: SQL woes
> 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
------- End of Original Message -------
-----------------------------------------------
To post, send email to [EMAIL PROTECTED]
To unsubscribe:
Send UNSUBSCRIBE to [EMAIL PROTECTED]
To subscribe / unsubscribe: http://www.dfwcfug.org
