[SQL] JOIN joint zu oft

[Medium]

Aloah!

Ich breche mir grad einen mit einem SQL-Statement ab. Ich habe folgende 2 Tabellen:

rkohis (Rezeptkopf-Historie)
- V_Dat (Vorgabedatum)
- Rez_Nr (Rezept-Nummer)
- Charge (frei vergebene Chargennummer)
- Rez_Name (String, Klartextname des Rezeptes)
- Komment (beliebiger Kommentar)

rpohis (Rezeptpositions-Historie)
- V_Dat (Vorgabedatum)
- Komp_Nr (Komponentennummer; ein Rohstoff des Rezeptes)
- Charge (frei vergebene Chargennummer)
- Rez_Nr (Rezept-Nummer)
- Anteil_Ist (verwogene Menge)

Ich möchte nun eine Abfrage haben, die ausgibt wie viel (Summe von Anteil_Ist) von jeden Rezept jemals produziert wurde, und in viel vielen Chargen das geschah. Folgendes Statement hab ich bisher:

SQL-Code:

SELECT
  k.REZ_NAME AS Rezeptname,
  k.KOMMENT AS Kommentar,
  COUNT(p.CHARGE)*1.0 AS Anzahl_Chargen,
  ROUND(SUM(p.ANTEIL_IST))*1.0 AS Menge
FROM
  rpohis p JOIN rkohis k ON p.REZ_NR = k.REZ_NR AND p.V_Dat = k.V_Dat
GROUP BY
  k.REZ_NAME
ORDER BY
  k.REZ_NAME

Das Problem ist, dass wenn ein Rezept z.B. 4 einzelne Komponenten hat ich am Ende den vierfachen Wert für "Anzahl_Chargen" herausbekomme.
Gruppiere ich zusätzlich nach "Komp_Nr", stimmen die jeweiligen Mengen, aber für jede Komponente ist natürlich die volle Anzahl Chargen eingetragen, die ohne diese Gruppierung zusammenaddiert werden - das darf nicht.

Kopf raucht. Wie kann ich das erreichen?

[p80286]

Kennt Mysql

Code:

Count distinct

das sollte dann besser passen

Gruß
K-H

[Medium]

Kennt es, hat aber ein fieses Problem: Es können mehrere Vorgaben mit gleicher Chargennummer (sie ist ja frei vergebbar - frag mich nicht warum) passieren, wovon dann immer nur eine gezählt wird. In meinen Testdaten sind solche Fälle, und dort bekomme ich dann zu wenig raus.

Wo es zuerst 56 war aber 14 sein sollte, sind es jetzt 11 - die Chargennummern 1, 2 und 3 sind doppelt vergeben -> 3 zu wenig

[mkinzler]

Vielleicht wäre in diesem besonderen Fall ein Subselect für die Summen geeigneter

[Medium]

Genau die kennt MySQL 4.1 leider nicht meine ich

[mkinzler]

Oder ein Join auf einen View über die Summen oder eine derived table

[p80286]

rkohis (Rezeptkopf-Historie)
- V_Dat (Vorgabedatum)
- Rez_Nr (Rezept-Nummer)
- Charge (frei vergebene Chargennummer)
- Rez_Name (String, Klartextname des Rezeptes)
- Komment (beliebiger Kommentar)

rpohis (Rezeptpositions-Historie)
- V_Dat (Vorgabedatum)
- Komp_Nr (Komponentennummer; ein Rohstoff des Rezeptes)
- Charge (frei vergebene Chargennummer)
- Rez_Nr (Rezept-Nummer)
- Anteil_Ist (verwogene Menge)


Wenn ich das richtig verstanden habe sind die oben gefetteten Felder für die eindeutuge Identifizierung notwendig, dann sollten die auch in einem Join auftauchen.

Wenn Du dann Komp_NR/Anteil_ist Zählen Summieren willst, sollte das passen.
Wenn die Charge mehrfach vergeben wurde, dann fällt die als "Zähler" einfach aus.

Wie verhält sich denn die Charge zur Rez_Nr ?
Letztlich kannst Du nur das aus der DB heraus holen was irgendwann einmal da hinein gstopft wurde.

Gruß
K-H

[Medium]

Zitat von mkinzler:

Oder ein Join auf einen View über die Summen oder eine derived table

Uhlala, Neuland für den kleinen Medium . Müsst ich erstmal eine kleine Lern-Session für einlegen.

@p80286: Völlig richtig, die 3 Angaben bilden den Index. Übrigens: Die Chargennummer ist nicht eine frei vergebene, sondern ein Produktionsauftrag kann für N Chargen gestartet werden. Für jede Charge wird dann ein neuer Kopf+Positionen eingetragen, und die Chargennummer ist dann fortlaufend. Da das Vorgabedatum aber für jeden Chargenstart, nicht für den ersten tatsächlichen Auftragsstart eingetragen wird, ist das Feld für eine Eindeutigkeitsbeziehung eigentlich überflüssig.
Anfügen von "AND p.Charge = k.Charge" im JOIN bewirkt daher auch leider keine Änderung

Wenn ich irgendwie 2 Schritte machen könnte, wäre das ganze vergleichsweise einfach. Ein Subselect wäre z.B. genial, dann wär's ein Kinderspiel. Ich fürchte ich muss mich tatsächlich in Views einlesen. Wobei ich immernoch der Meinung bin, dass das eigentlich alles in einem Statement möglich sein müsste. So rein instinktiv


Edit
Ich bin ja schon ein Trottel. MySQL 4.1 kennt Subselects! Aber... leider sind die keine Lösung
Folgendes Statement bringt das richtige Ergebnis:

SQL-Code:

SELECT
  k.REZ_NAME AS REZ_NAME,
  k.KOMMENT AS TEXT,
  (SELECT COUNT(Rez_Name) FROM rkohis WHERE Rez_Nr = 1)*1.0 AS Anz,
  ROUND(SUM(p.ANTEIL_I))*1.0 AS Menge
FROM
  rpohis p JOIN rkohis k ON p.REZ_NR = k.REZ_NR AND p.V_Dat = k.V_Dat AND k.Charge = p.Charge
WHERE
  p.Rez_Nr = 1
GROUP BY
  k.REZ_NAME
ORDER BY
  k.REZ_NAME

Aber! Die WHERE-Clause im äusseren Statement ist nicht immer nur so simpel. Der Anwender kann wählen welche Rezepte alle genommen werden sollen, so dass es auch mal heissen kann: "WHERE p.Rez_Nr = 1 OR p.Rez_Nr = 8 OR p.Rez_Nr = 21 OR ... OR ..."
Das ist mit dem Subselect dann schon wieder nicht mehr so einfach vereinbar fürchte ich

[mkinzler]

Bei einer derived table wird eine Abfrage wie eine Tabelle verwendet

SQL-Code:

select 
    ...
from
    tabelle1 a on ( 
        select <gruppierungsfeld>, sum( <feld>) ... 
    from 
        tabelle2 
    group by
        <Gruppierungsfeld>
    ) b on b.<Gruppierungsfeld> = a.<pk>;

[Medium]

AHHHH! Genial!

SQL-Code:

SELECT
  Rez_Name,
  Text,
  Anz,
  SUM(Menge) AS Menge
FROM (
  SELECT
    k.REZ_NAME AS REZ_NAME,
    k.KOMMENT AS TEXT,
    COUNT(p.CHARGE)*1.0 AS Anz,
    ROUND(SUM(p.ANTEIL_I))*1.0 AS Menge
  FROM
    rpohis p JOIN rkohis k ON p.REZ_NR = k.REZ_NR AND p.V_Dat = k.V_Dat AND k.Charge = p.Charge
  WHERE
    p.Rez_Nr = 1
  GROUP BY
    k.REZ_NAME, p.Komp_Nr
  ORDER BY
    k.REZ_NAME) AS tmp
GROUP BY
  Rez_Name

Daaaaaas funktionieeeert! Ich danke euch ganz ganz kräftig!