left outer join issue

[robert030975]

bit stuck!!!

I have two temp tbles warehouse in and warehouse out which I want to merge

so if I do a count * based on a specific week I get 857 rows


however when I do

select a.* from #tf_wh_out a left outer join
#tf_wh_in b on
a.despatchwhse = b.code and
a.product = b.product_code and
a.pfinish = b.pfinish and
a.fiscal_week = b.fiscal_week and
a.fiscal_year = b.fiscal_year
where a.fiscal_week = 10 and a.fiscal_year = '07 08'

I still get 857 which is rubbish as I would expect more!!

if I put b.product_code is null it gives me 656 ??

is there a limitation on doing a left outer ??

 

[r937]

LEFT OUTER JOIN always returns every row from the left table, and your left table appears to have 656 rows

if you join, but select only columns from the left table, of course you are going to get as many rows in the result as there are in the join



r937.com | rudy.ca

 

[Chopstik]

It would appear that your table on the left (table a) has 857 records. When you do the left join as you originally stated, it will return every record in table a (all 857 records). However, when you add the b.product_code is null to your WHERE statement, you are then making your statement into an INNER JOIN. The INNER JOIN will only join records where the two match. If you still want to have the 857 records using your original LEFT JOIN, you would do better to do something like the following - which will still allow you to filter the table on the other side of your join without making it an INNER JOIN (not tested):

select a.* from #tf_wh_out a left outer join
#tf_wh_in b on
a.despatchwhse = b.code and
a.product = b.product_code and [COLOR=red]b.product_code IS NULL and [/color] 
a.pfinish = b.pfinish and
a.fiscal_week = b.fiscal_week and
 a.fiscal_year =  b.fiscal_year
where a.fiscal_week = 10 and a.fiscal_year = '07 08'

Out of curiosity, why would you expect more than 857 records?

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill

 

[r937]

chopstik, i'm guessing the [blue]b.product_code IS NULL[/blue] condition was in the WHERE clause, and if this guess is correct, then the modified query returns unmatched rows from the left table

i'm guessing the left table has 656 rows, but if you look at the original query (without the [blue]b.product_code IS NULL[/blue] condition) it's returning 857 rows because of the join, however, this is not immediately apparent because only columns from the left table are in the SELECT

original poster can resolve this easily, eh

r937.com | rudy.ca

 

[Chopstik]

Rudy, good point. I was thinking about using a filter from the right side of a left join making an outer join into an inner join by default. I hadn't thought about it as you've pointed out, though I'm still trying to follow the logic... Either way, the OP can certainly resolve it easily enough.

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill

 

[robert030975]

Thanks guys sorry was really T'd off on Sat and should have been more clearer!!

I totally agree but somethings not right!!

You' ve got two tables ins say 48,000 rows and outs 1,046,000 rows

if I do a count on ins 301 and outs 857 for week 10.

now we can deduce that ins will have some products that exist on out and some products that don't exist on out. the same applies for the same out against in.

if I do a straight left outer join and product is null
I get 857 records now I know that I should have for that week 958 records


so what I would have is

OUT
WH Product Pfinish fiscal_week Fiscal Year Qunatity_out

123 abcd a 10 07 08 10
123 abce b 10 07 08 NULL
123 abcc b 10 07 08 5

IN
WH Product Pfinish fiscal_week Fiscal Year Qunatity_IN

123 abcd a 10 07 08 NULL
123 abce b 10 07 08 45
123 abcc b 10 07 08 2


both in and out would show the same amount 958 of rows which then you can do a straight inner join.


a simple test on left outer is
Declare @Table1 Table(Value int)
Declare @Table2 Table(Value int)

Insert Into @Table1 Values(1)
Insert Into @Table1 Values(2)
Insert Into @Table1 Values(3)
Insert Into @Table1 Values(4)
Insert Into @Table1 Values(5)

Insert Into @Table2 Values(1)
Insert Into @Table2 Values(2)
Insert Into @Table2 Values(4)
Insert Into @Table2 Values(5)

Select T1.*
From @Table1 As T1
Left Join @Table2 T2
On T1.Value = T2.Value is null --- brings all 1-5
--Where T2.Value Is NULL this would bring back 3.

so if I do select a.* from #tf_wh_out a left outer join
#tf_wh_in b on
a.despatchwhse = b.code and
a.product = b.product_code and b.product_code IS NULL and
a.pfinish = b.pfinish and
a.fiscal_week = b.fiscal_week and
a.fiscal_year = b.fiscal_year
where a.fiscal_week = 10 and a.fiscal_year = '07 08'

I get 857 arrrh


now the strange thing is if I feed the #in to a temp for week 10 and #out into a temp for week 10#

select a.* from #tf_wh_out1 a left outer join
#tf_wh_in1 b on
a.despatchwhse = b.code and
a.product = b.product_code and b.product_code IS NULL and
a.pfinish = b.pfinish and
a.fiscal_week = b.fiscal_week and
a.fiscal_year = b.fiscal_year
where a.fiscal_week = 10 and a.fiscal_year = '07 08'

Is still get 857 but if I do a select whse, product,
but if I do where b.product_code IS NULL i get 656


pfinsh, week, year
into #product
then do an insert #product
select whse, product, pfinsh, week, year fron
#tf_wh_in1 left_outer_join
#product on a.product = b.product_code and b.product_code IS NULL and
a.pfinish = b.pfinish and
a.fiscal_week = b.fiscal_week and
a.fiscal_year = b.fiscal_year


then I get the magic 958!!!



I worte a dynamic sp to feed week by week but would still like a solution.

bit long winded but thanks

 

[r937]

you would like a solution? i would like to understand your data!

there is a small problem here:

ON ... a.product = b.product_code
and b.product_code IS NULL

if b.product_code really is NULL, which is rather hard to believe, since it's quite likely (part of) the primary key, then there's no way that you will get a.product = b.product_code, because NULL is not equal to anything, not even another NULL


if you are dealing with join columns, the IS NULL test always belongs in the WHERE clause

r937.com | rudy.ca

 

[cmmrfrds]

Robert, are you trying to include non-matches from both tables. If so, that would be a "full outer join". If this is the case the classic way of doing this is with a Union.

select *
from
(
select * from table a
left outer join table b
on a.id = b.id
Union
select * from table a
right outer join table b
on a.id = b.id
) as dt

 

[robert030975]

I honestly thought union at first but for what ever reason went down the outer and temp path arrr

I 'll try it and let you know!!

Cheers Robert