Hi guys, I have problem using this full join, can anybody help me ?
I'm using VB6 and SQL 2000.
I want to join tabel a and tabel b, very simple, but the result is not what I expected.
Code:
SELECT a.PCode1, (CASE WHEN a.PBalance IS NULL THEN 0 ELSE a.UnUsedQty END) AS QB, 
SUM(CASE WHEN i.TType = '+' THEN i.Qty ELSE 0 END) AS QI, 
SUM(CASE WHEN i.TType = '-' THEN i.Qty ELSE 0 END) AS QO, 0 AS QE
FROM ItemBal a FULL JOIN InvTr i ON a.Pcode1 = i.Pcode1
WHERE (a.CYear = 2008 AND a.CMonth = 8) AND (i.SlipDate BETWEEN '9/1/2008' AND '9/15/2008') AND (a.PCode1 BETWEEN 'S' AND 'T')
GROUP BY a.Pcode1, a.PBalance
ORDER BY a.PCode1
The data would be like in this order :
ItemBal InvTr
Item1 5 Item2 + 2
Item2 3 Item2 - 1
Item3 1 Item3 + 4
Item4 + 2

And the result I want is :
Item1 5 0 0 0
Item2 3 2 1 0
Item3 1 4 0 0
Item4 0 2 0 0

Instead I got :
Item2 3 2 1 0
Item3 1 4 0 0

Oh, yes, I put 0 AS QE becoz I don't know how the formula in SQL command. It's actually QB+QI-QO. Can anyone help me with my query ? Thanks......