1. Registered User
Join Date
Feb 2004
Location
Poland
Posts
96

i'm shure it's some smal stiupid mistake bat I can't find it, PLZ help.

1)
select komorka from #plantemp
--result

komorka

09
10
I-P
II-P
III-P
SI/1
SI/2
SI/3

2)
select komorka,ustalenia from analiza_1 a where a.koniec between '20040701'and '20040731'

komorka ustalenia
SI/1 788138.9300
SI/2 46638.4900
SI/2 16218.4000
08 .0000

3)
select p.komorka,isnull(sum(ustalenia),0)
from #plantemp p left join analiza_1 a on p.komorka=a.komorka
where a.koniec between '20040701'and '20040731'
group by p.komorka

komorka ustalenia (sum)

08 .0000
SI/1 788138.9300
SI/2 62856.8900

I need all rows from table 1 bat right and left join gives me the same results, WHY

2. Registered User
Join Date
Jul 2004
Posts
13
I don't understand what you mean. You've stated GROUP BY, which collapses the two rows with komorka SI/2 and you have the sum of ustalenia for those two rows.

Basically, you've got the same result as from query 2) but grouped by komorka. This seems to be entirely correct.

It may be just me who doesn't see it, but if not, maybe you can post something to clarify a bit what the problem is?

3. Registered User
Join Date
Jul 2004
Posts
13
Perhaps what you mean is that you need the komorka rows from #plantemp that don't exist in analiza_1, with the sum 0?

If so, it's correct to use a LEFT join (keeps all rows on the left side), but you still got to watch it so your filtering doesn't remove it. In your case

where a.koniec between '20040701'and '20040731'
will remove the left-joined row since a.koniec is null for this row. Try to modify it to

where (a.koniec between '20040701'and '20040731' OR a.koniec is null)

in this case.

4. Registered User
Join Date
Feb 2004
Location
Poland
Posts
96
Thanx dagjo
it works

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•