Join über 3 Tabellen

[Hansa]

Hi,

folgendes Problem: Artikel, Preisgruppen und Sonderpreis - Table. Jeder Kunde hat x Datensätze mit Preisen. Ich gehe also über die Sonderpreis-Table. Ist der Preis <> 0, so zeige ich ihn an. Ist er = 0 so brauche ich den zugehörigen Preis des Artikels aus der Preisgruppen-Tabelle. Ich mache deshalb einen INNER JOIN über die Sonderpreise. Hierdurch zeigt er mir alle Sonderpreise eines Kunden an.

Wie kann ich es nun bewerkstelligen, im Falle, daß der Sonderpreis 0 ist, den Preisgruppen-Preis anzuzeigen ?

Delphi-Quellcode:

ArtDS.SelectSQL.Text := 'SELECT ART.*,SP.* FROM SP INNER JOIN SP';
    ArtDS.SelectSQL.Text := ArtDS.SelectSQL.Text + ' ON ART.ID=SP.ID_ART';
    ArtDS.SelectSQL.Text := ArtDS.SelectSQL.Text + ' WHERE ID_KUNDE = '+
                            KuDS.FieldByName ('ID').AsString+' ORDER BY NR';

[Sharky]

Hai Hansa,

unterstützt deine Datenbank eventuell if oder case Befehle?

[Robert_G]

Du kannst ja mal schauen, ob einer der beiden Vorschläge läuft (Oracle syntax)...

SQL-Code:

SELECT ART.*,
       SP.*,
       Decode(SP.Preis, 0, ART.Preis, SP.Preis) SonderPreis
FROM   SP Inner Join SP On ART.ID = SP.ID_ART
WHERE  ID_KUNDE = :i_ID
ORDER  By NR

SQL-Code:

SELECT ART.*,
       SP.*,
       Case When SP.Preis = 0 Then
           ART.Preis
          Else
           SP.Preis
       End SonderPreis
FROM   SP Inner Join SP On ART.ID = SP.ID_ART
WHERE  ID_KUNDE = :i_ID
ORDER  By NR

[Hansa]

Es ist Interbase. Wie es aussieht wird CASE nicht unterstützt, wohl aber IF. Weiter bin ich noch nicht. Vielleicht habe ich die Frage auch falsch gestellt.

Code:

Sonderpreis --> <> 0 --> anzeigen 
            
            --> = 0  --> in anderer Tabelle (Preisgruppe) suchen

[Robert_G]

Das hatte ich so schon verstanden. Da ich deine Spaltennamen nicht kenne, habe ich sie einfach Preis bzw. Sonderpreis (in der Abfrage) genannt.
Zu einer der beiden Herangehensweisen wird es in IB bestimmt eine Lösung geben.

[DelphiDeveloper]

btw mit firebird 1.5 hättest du ne case anweisung

bei interbase würde ich das über einen
union machen

1. Teil
preis <> 0 mit sonderpreise

2. Teil
Preis = 0 mir preisgruppenpreisen

[Robert_G]

Jupp, man könnte es ja auch mit einem UNION-SELECT lösen:

SQL-Code:

SELECT ART.*, 
       SP.*, 
       ART.Preis SonderPreis 
FROM   SP Inner Join SP On ART.ID = SP.ID_ART 
WHERE  ID_KUNDE = :i_ID And SP.Preis = 0
UNION
SELECT ART.*, 
       SP.*, 
       SP.Preis SonderPreis 
FROM   SP Inner Join SP On ART.ID = SP.ID_ART 
WHERE  ID_KUNDE = :i_ID  And SP.Preis <> 0

ORDER  By NR

[Hansa]

wenn ich das nun so mache:

SQL-Code:

SELECT ART.*,SP.* FROM ART INNER JOIN SP ON ART.ID=SP.ID_ART
WHERE ID_KUNDE = 661 AND VP <> 0
UNION
SELECT ART.*,PG.* FROM ART INNER JOIN PG ON ART.ID=PG.ID_ART
WHERE PGNR = 1

kommt diese Fehlermeldung:

count of column list and variable list do not match.

[Robert_G]

Bei einem UNION SELECT müssen die Spalten der beiden Abfragen den gleichen Datentype haben (jeweils die, die untereinander stehen).

[Hansa]

Stimmt, das muß ja alles ziemlich gleich sein.

SQL-Code:

SELECT ART.NR,ART.bez, SP.ID, SP.VP FROM
ART INNER JOIN SP ON ART.ID=SP.ID_ART
WHERE ID_KUNDE = 661 AND VP <> 0
UNION
SELECT ART.NR,ART.bez, ARTPG.ID, ARTPG.PG FROM
ART INNER JOIN ARTPG ON ART.ID=ARTPG.ID_ART

Hiermit erhalte ich schon etwas in der gewünschten Richtung, nur leider viel zu viel, denn :

WHERE ID_KUNDE = 661 AND VP = 0

läßt sich nicht im 2. Abschnitt einbauen. Die Preisgruppen haben nichts mit den Kunden zu tun, die Sonderpreise aber sehr wohl. Ich müßte nun also das Feld ID_KUNDE neutralisieren, dann müßte es fast so gehen. :gruebel:

[Sharky]

Hai Hansa,

mal eine ganz andere Frage:

Warum machst Du nicht einfach zwei getrennte Abfragen auf die zwei Tabellen?

[DelphiDeveloper]

das muss doch so gehen!

Code:

SELECT ART.NR,ART.bez, SP.ID, SP.VP 
  FROM  ART, SP 
  WHERE (ART.ID=SP.ID_ART ) AND 
        (ID_KUNDE = 661) AND (VP <> 0) 
UNION 
SELECT ART.NR,ART.bez, ARTPG.ID, ARTPG.PG 
  FROM ART, ARTPG 
  WHERE (ART.ID=ARTPG.ID_ART) AND  
  (ID_KUNDE = 661) AND (VP = 0) 
ORDER BY 1

SORRY dein problem mit der ID_KUNDE hatte ich oben überlesen

[Hansa]

Zitat:

Zitat von Sharky

Warum machst Du nicht einfach zwei getrennte Abfragen auf die zwei Tabellen?

Zwei Alternativen gibt es schon:

1. ich mache ein zweites Select, sobald ich auf einen Preis = 0 stoße (heißt: KEIN Sonderpreis, Standardpreis aus Preisgruppen-Table).Mache ich so im Moment. Dauert aber unnötig lange, wegen dauerndem öffnen und schließen des Preisgruppen-Dataset.

2. Probiere ich gleich mal aus: ich lese ALLE Sätze der Preisgruppe. Dann hätte ich die 2 Datasets immer geöffnet. Ist aber auch nicht schön und auch wohl lahm. Man stelle sich einmal einen Kunden vor, der 5 Preise hat (3 Sonderpreise, 2 normale). Die Sonderpreise hätte ich sofort und für die beiden anderen muß ich jeweils mehrere 1000 Preisgruppen durchwühlen.

Zudem bin mir ziemlich sicher, daß das auch besser geht.

[DelphiDeveloper]

ich habe mal eine stored proc auf interbase gemacht, mit der muesste es aber
jetzt gehen

Code:

CREATE PROCEDURE GET_ARTIKEL (
    IN_ID_KUNDE INTEGER)
RETURNS (
    OUT_NR INTEGER,
    OUT_BEZ VARCHAR(30),
    OUT_ID INTEGER,
    OUT_PREIS NUMERIC(9,2))
AS
/* HILFSVARIABLEN FUER ERSTE ABFRAGE */
DECLARE VARIABLE OUT_HNR INTEGER;
DECLARE VARIABLE OUT_HBEZ VARCHAR(30);
DECLARE VARIABLE OUT_HID INTEGER;
DECLARE VARIABLE OUT_HPREIS NUMERIC(9,2);
BEGIN
 FOR SELECT ART.NR,ART.BEZ, SP.ID, SP.VP
      FROM ART , SP
      WHERE (ART.ID=SP.ID_ART ) AND
             SP.ID_KUNDE = :IN_ID_KUNDE
      INTO :OUT_HNR, :OUT_HBEZ, :OUT_HID, :OUT_HPREIS
 DO
  /* ist der preis <> 0 dann gebe hilfsvariablen aus */
  IF (OUT_HPREIS <> 0) THEN
  BEGIN
    OUT_NR = :OUT_HNR;
    OUT_BEZ = :OUT_HBEZ;
    OUT_ID = :OUT_HID;
    OUT_PREIS = :OUT_HPREIS;
    SUSPEND;
  END
  ELSE
  BEGIN
    /* preis offensichtlich 0 -> hole preis von artpg direkt in ausgabevariable */
    FOR SELECT ART.NR,ART.BEZ, ARTPG.ID, ARTPG.PG
    FROM ART, ARTPG
    WHERE (ART.ID = ARTPG.ID_ART) AND ART.NR = :OUT_HNR  /* das ist die art.nr bon oben */
    INTO :OUT_NR, :OUT_BEZ, :OUT_ID, :OUT_PREIS
   DO
    SUSPEND;
  END


END

[Robert_G]

Dein Problem bestand darin, dass du in der 2. Abfrage auch die Artikel bekommst, die im ersten schon vorkommen.

SQL-Code:

SELECT Art.Nr
      ,Art.Bez
      ,Sp.Id
      ,Sp.Vp
FROM   Art INNER JOIN Sp ON Art.Id = Sp.ID_Art
WHERE  Id_Kunde = :i_ID_Kunde And Vp <> 0
UNION
SELECT Art.Nr
      ,Art.Bez
      ,ArtPG.ID
      ,ArtPG.PG
FROM   Art INNER JOIN ArtPG ON Art.ID = ArtPG.ID_Art
WHERE  Art.ID Not In (SELECT subSP.ID_Art
                      FROM   Sp subSP
                      WHERE  subSP.ID_Kunde = :i_ID_Kunde)

Zitat:

Zitat von Hansa

...Ist aber auch nicht schön und auch wohl lahm. ...

Wenn du die Kunden ID als Parameter übergibst und einen Index auf ID_Art legst, dürfte das da oben ziemlich flink sein.
Beim ersten Ausführen normal schnell, danach kann der Server auf Werte aus dem Cache zurückgreifen (dank der Verwendung des Parameters) -> viel schneller (abhängig von Größe & Typ vom Arbeitsspeicher des Servers).
Der Index auf Art_ID soll im "NOT IN..."-sub select aus einem Table scan in einen Index scan machen -> nochmal schneller.

[Hansa]

Hi,

der Tip von DelphiDeveloper läuft schon. Allerdings bringt es nicht so viel wie erwartet. Das Schließen und Öffnen einzelner Datensätze scheint nicht so viel langsamer zu sein. Statt 20 Sek. dauert es immer noch 10. Oder ist das normal bei 500 Records ? :gruebel:

Das Beispiel von Robert_G läuft anscheinend in eine Endlosschleife. Allerdings weiß ich nicht genau, was das letzte Select mit dem subSP genau machen soll. Werden etwa nicht vorhandene IDs gesucht ? Wenn jemand 100 Preise, davon 50 Sonderpreise hat, so sind die anderen 50 schon da. Das Kennzeichen ist SP=0.

[DelphiDeveloper]

Zitat:

Das Schließen und Öffnen einzelner Datensätze scheint nicht so viel langsamer zu sein. Statt 20 Sek. dauert es immer noch 10. Oder ist das normal bei 500 Records ?

Das muss aber bei 500 rec viel schneller laufen! Hast du es mit meiner SP gemacht?
Wenn ja lass mal sehen wie sie jetzt aussieht

[Hansa]

so siehts aus:

SQL-Code:

BEGIN 
 FOR SELECT ART.ID, ART.NR,ART.BEZ, SP.VP
      FROM ART, SP 
      WHERE (ART.ID=SP.ID_ART ) AND 
             SP.ID_KUNDE = :ID_KUNDE 
      INTO :OUT_HARTID, :OUT_HNR, :OUT_HBEZ, :OUT_HPREIS
 DO 
begin
  OUT_NR = :OUT_HNR;
  OUT_BEZ = :OUT_HBEZ;
  OUT_PREIS = :OUT_HPREIS;
  /* ist der preis <> 0 dann gebe hilfsvariablen aus */
  IF (OUT_HPREIS <> 0) THEN BEGIN
    SUSPEND;
  END
  ELSE 
  BEGIN 
    /* preis = 0 -> hole preis von artpg direkt in ausgabevariable */
    FOR SELECT ARTPG.PG FROM ARTPG
    WHERE (ARTPG.ID_ART = :OUT_HARTID) AND (ARTPG
.PGNR = :PGNR)  /* das ist die art.nr von oben */
    INTO :OUT_PREIS
   DO 
    SUSPEND; 
  END 
end
END

Ich habe nicht viel gemacht.

[DelphiDeveloper]

ist das antwort-zeitverhalten im ibexpert den schneller oder dauert
es genauso lange wie in deine App?