SQL left join

[ahingerl]

Ich trau SQL keinen Meter mehr!

ich habe zwei SQL abfragen, der einzige unterschied ist, einmal nur nach Kunde gruppiert, das andere mal nach kunde und Artikel gruppiert. Sollte eigentlich beide male das gleiche Ergebniss sein. Aber dem ist nicht so.

select 
  x.stpknr,x.stidnr,
           sum(x.stuw01) as stuw01, sum(x.stuw02) as stuw02, sum(x.stuw03) as stuw03,
           sum(x.stuw04) as stuw04, sum(x.stuw05) as stuw05, sum(x.stuw06) as stuw06,
           sum(x.stuw07) as stuw07, sum(x.stuw08) as stuw08, sum(x.stuw09) as stuw09,
           sum(x.stuw10) as stuw10, sum(x.stuw11) as stuw11, sum(x.stuw12) as stuw12,
           sum(x.Vstuw01) as Vstuw01, sum(x.Vstuw02) as Vstuw02, sum(x.Vstuw03) as Vstuw03,
           sum(x.Vstuw04) as Vstuw04, sum(x.Vstuw05) as Vstuw05, sum(x.Vstuw06) as Vstuw06,
           sum(x.Vstuw07) as Vstuw07, sum(x.Vstuw08) as Vstuw08, sum(x.Vstuw09) as Vstuw09,
           sum(x.Vstuw10) as Vstuw10, sum(x.Vstuw11) as Vstuw11, sum(x.Vstuw12) as Vstuw12
from 
( select stpknr, stidnr,stuw01,stuw02,stuw03,stuw04,stuw05,stuw06,stuw07,stuw08,stuw09,stuw10,stuw11,stuw12, 
                 0 as Vstuw01, 0 as Vstuw02,0 as Vstuw03,0 as Vstuw04,0 as Vstuw05,0 as Vstuw06,
                 0 as Vstuw07, 0 as Vstuw08,0 as Vstuw09,0 as Vstuw10,0 as Vstuw11,0 as Vstuw12
  from cmddtar6_0.istopp where stpknr='1416250' and stygja='106' and stanws='VKS' and stfirm='777'
 union
  select stpknr, stidnr, 0 as stuw01, 0 as stuw02,0 as stuw03,0 as stuw04,0 as stuw05,0 as stuw06,
                 0 as stuw07, 0 as stuw08,0 as stuw09,0 as stuw10,0 as stuw11,0 as stuw12,
                 stuw01 as Vstuw01, stuw02 as Vstuw02, stuw03 as Vstuw03, stuw04 as Vstuw04, stuw05 as Vstuw05, stuw06 as Vstuw06,
                 stuw07 as Vstuw07, stuw08 as Vstuw08, stuw09 as Vstuw09, stuw10 as Vstuw10, stuw11 as Vstuw11, stuw12 as Vstuw12
  from cmddtar6_0.istopp where stpknr='1416250' and stygja='105' and stanws='VKS' and stfirm='777') as x
group by stpknr,stidnr

select 
  x.stpknr,sum(x.stuw01) as stuw01, sum(x.stuw02) as stuw02, sum(x.stuw03) as stuw03,
           sum(x.stuw04) as stuw04, sum(x.stuw05) as stuw05, sum(x.stuw06) as stuw06,
           sum(x.stuw07) as stuw07, sum(x.stuw08) as stuw08, sum(x.stuw09) as stuw09,
           sum(x.stuw10) as stuw10, sum(x.stuw11) as stuw11, sum(x.stuw12) as stuw12,
           sum(x.Vstuw01) as Vstuw01, sum(x.Vstuw02) as Vstuw02, sum(x.Vstuw03) as Vstuw03,
           sum(x.Vstuw04) as Vstuw04, sum(x.Vstuw05) as Vstuw05, sum(x.Vstuw06) as Vstuw06,
           sum(x.Vstuw07) as Vstuw07, sum(x.Vstuw08) as Vstuw08, sum(x.Vstuw09) as Vstuw09,
           sum(x.Vstuw10) as Vstuw10, sum(x.Vstuw11) as Vstuw11, sum(x.Vstuw12) as Vstuw12
from 
( select stpknr, stuw01,stuw02,stuw03,stuw04,stuw05,stuw06,stuw07,stuw08,stuw09,stuw10,stuw11,stuw12, 
                 0 as Vstuw01, 0 as Vstuw02,0 as Vstuw03,0 as Vstuw04,0 as Vstuw05,0 as Vstuw06,
                 0 as Vstuw07, 0 as Vstuw08,0 as Vstuw09,0 as Vstuw10,0 as Vstuw11,0 as Vstuw12
  from cmddtar6_0.istopp where stpknr='1416250' and stygja='106' and stanws='VKS' and stfirm='777'
 union
  select stpknr, 0 as stuw01, 0 as stuw02,0 as stuw03,0 as stuw04,0 as stuw05,0 as stuw06,
                 0 as stuw07, 0 as stuw08,0 as stuw09,0 as stuw10,0 as stuw11,0 as stuw12,
                 stuw01 as Vstuw01, stuw02 as Vstuw02, stuw03 as Vstuw03, stuw04 as Vstuw04, stuw05 as Vstuw05, stuw06 as Vstuw06,
                 stuw07 as Vstuw07, stuw08 as Vstuw08, stuw09 as Vstuw09, stuw10 as Vstuw10, stuw11 as Vstuw11, stuw12 as Vstuw12
  from cmddtar6_0.istopp where stpknr='1416250' and stygja='105' and stanws='VKS' and stfirm='777') as x
group by stpknr

Bitte um euere Hilfe.

Danke.

[Fuerchau]

Nun, dazu muss man den UNION verstehen.
Default heißt dieser UNION DISTINCT !
Wenn also im 1. Select und im 2. Select identische Sätze (also alle Felder) auftreten, wird nur einer genommen.
Da du aber alle Daten benötigst musst du dies explizit erwähnen mit UNION ALL !