FULL JOIN but not return full ?

[Batman108]

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.

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......

[techgnome]

Well, I'm going to guess it's because Pcode1 can only be between S and T... which means only PCode1's S & T will be returned....

-tg

[Batman108]

Er, no, actually Item1, Item2 just for illustration. PCode1 itself contain something like :
RMBO008
RMGL156
SPBM007
SPBS029
SPV01001
SPW01332
WFGEN032

So I just want to check all PCode1 begin with S. Offcoz I can also use LEFT(a.PCode1,1)='S' or other methods. I quote this from Szlamany :

FULL JOIN or FULL OUTER JOIN.
A full outer join returns all rows in both the left and right tables. Any time a row has no match in the other table, the select list columns from the other table contain null values. When there is a match between the tables, the entire result set row contains data values from the base tables.

The first post is hard to see, here I rearrange of what I want :

The data would be like in this order :
ItemBal                      InvTr
PCode1      PBalance         PCode1      TType     Qty
SPBM007            5         SPBS029       +         2
SPBS029            3         SPBS029       -         1
SPW01332           1         SPV01001      +         4
                             SPW01332      +         2

And the result I want is :
PCode1      QB   QI   QO   QE
SPBM007      5    0    0    0
SPBS029      3    2    1    0
SPV01001     0    4    0    0
SPW01332     1    2    0    0

Instead I got :
PCode1      QB   QI   QO   QE
SPBS029      3    2    1    0
SPW01332     1    2    0    0

[techgnome]

I got all that.... but you're not going to get the rows you want because your WHERE clause is looking for where PCode1 (from ItemBal) is between S & T... BUT since SPV01001 DOESN'T exist in ItemBAl, it returns NULL (as per the quote from above), which is NOT between S & T. So it gets filtered out.

What I think you need is logic that reads, if ItemBal.PCode1 is not null, use it, if it is null then use the PCode1 from InvTr.... like this:

AND (ISNULL(a.PCode1, i.PCode1) BETWEEN 'S' AND 'T')

-tg

[Batman108]

TG, sorry for misunderstood your answer.
I tested your given code, indeed it show some of items that didn't show up in my first query but still not what I expected.
After checking, I found out it's because I've another filter : (i.Slipdate between '9/1/2008' and '9/15/2008').
For example SPBM007 is found in ItemBal and actually in InvTr also have records but field Slipdate is '9/20/2008', then this won't show up in my query.
If I can use ISNULL() for PCode1, how can I apply this to SlipDate as there's no date field in ItemBal ?
Please help.....

[techgnome]

Like this:

...AND ((i.SlipDate BETWEEN '9/1/2008' AND '9/15/2008') OR (i.SlipDate IS NULL)) ...

-tg

[Batman108]

TG, thanks, I'm getting there but not quite yet.
This is my latest code :

SELECT isnull(a.pcode1, i.PCode1) AS PCode, isnull(a.pbal, 0) 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 tb1 a FULL JOIN
tb2 i ON a.Pcode1 = i.Pcode1 AND a.cYear = '2008' AND a.cMonth = '8'
WHERE ((i.SlipDate BETWEEN '9/1/2008' AND '9/15/2008') OR
((i.SlipDate IS NULL) AND a.cyear = '2008' AND a.cmonth = '8')) AND (isnull(a.PCode1, i.pcode1) BETWEEN 'I' AND 'J')
GROUP BY a.Pcode1, i.pcode1, a.pbal
ORDER BY a.PCode

and the result is in my attachment.

As you can see, Item1 didn't show up but it actually should. Where did I miss ?

[techgnome]

you have a couple of parenthesis off there.... your where should look like this:

WHERE  ((i.SlipDate BETWEEN '9/1/2008' AND '9/15/2008') OR (i.SlipDate IS NULL)) AND ((a.cyear=2008) AND (a.cmonth=8)) AND (ISNULL(a.PCode1, i.PCode1) BETWEEN 'I' AND 'J')

That's assuming that cyear and cmonth are numeric fields ... ??? I see that you also have hte cmonth and cyear as part of the join. While that works, it becomes redundant in the where clause... take it out of one or the other - you don't need it in both places.

-tg

[Batman108]

TG, thanks, but I test your last code it given me result exactly like my very first query.
I put a.cyear and a.cmonth again in my WHERE because otherwise the result will also show Item2 with cyear='2008' and cmonth='7' which I don't want. Oh yes, Cyear and CMonth is char.
TG, I think I'm gonna surrender and give this up I'll try to put InvTr into temp table and then join it with ItemBal. I think this will have faster result ?
Thanks for your help, TG....