Results 1 to 4 of 4

Thread: left/right join

  1. #1
    Join Date
    Feb 2004
    Location
    Poland
    Posts
    96

    Unanswered: left/right join

    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. #2
    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. #3
    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. #4
    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
  •