Information ausblenden
Willkommen im Forum für alle Datenbanken! Registriere Dich kostenlos und diskutiere über DBs wie Mysql, MariaDB, Oracle, Sql-Server, Postgres, Access uvm

Datenbankabfrage über 3 Tabellen

Dieses Thema im Forum "MySQL und MariaDB" wurde erstellt von neuling88, 12 Februar 2013.

  1. neuling88

    neuling88 Benutzer

    Hallo Leute,

    Ich arbeite an einem Projekt wo eine MySQL Datenbank zum Einsatz kommt. Der grösste Teil der Datenbankabfragen habe ich selbst hingekriegt, doch ein paar Abfragen waren dann doch zu kompliziert... Ich hoffe ihr könnt mir da helfen :)

    Ich habe folgende Tabellen/Spalten (nur der Teil, der wichtig ist):
    • Tabelle "parts": id, instock, [...]
    • Tabelle "orderdetails": id, part_id, [...]
    • Tabelle "pricedetails": id, orderdetails_id, price, min_discount_quantity, price_related_quantity, [...]
    In der Tabelle "parts" sind Artikel abgelegt. In "orderdetails" sind Einkaufsinformationen (Lieferant + Bestellnummer) abgelegt, mit "part_id" ist die Tabelle mit der Tabelle "parts" verknüpft. Ein Artikel kann gar keine bis unbegrenzt viele Einkaufsinformationen haben. In der Tabelle "pricedetails" sind dann die Preise für verschiedene Bestellmengen abgelegt, immer auf eine Einkaufsinformation bezogen. Eine Einkaufsinformation kann gar keine bis unbegrenzt viele Preisinformationen haben.

    Beispieldatensätze:
    • Artikel "A" gibts bei Lieferant "X", 1 Stk. für 10 Euro, 10 Stk. für 90 Euro und 100 Stk. für 800 Euro, oder bei Lieferant "Y" für 9 Euro/Stk.
    • Artikel "B" gibts bei Lieferant "Z" (keine Preisinformationen)
    • Artikel "C" (keine Einkaufsinformationen)
    So, und nun möchte ich alle Artikel bekommen, die keine Preisinformationen haben. Also alle Artikel, die keinen Eintrag in der Tabelle "pricedetails" (via "orderdetails") besitzen.

    Mein erster Ansatz sieht so aus, da fehlt aber wohl noch ein bisschen was:
    Code:
    SELECT parts.id FROM parts
    LEFT JOIN orderdetails ON orderdetails.part_id = parts.id
    LEFT JOIN pricedetails ON pricedetails.orderdetails_id = orderdetails.id
    WHERE pricedetails.id IS NULL OR orderdetails.id IS NULL
    ORDER BY parts.name ASC
    Ausserdem würde ich gerne noch abfragen können, wieviel alle Artikel, die im Lager sind, zusammen kosten. In der Tabelle "parts" ist die vorhandene Menge in der Spalte "instock" abgelegt, mit dieser Anzahl soll der Artikelpreis multipliziert werden. Als Preis soll möglichst der Durchschnittspreis genommen werden, über alle verfügbaren Einkaufsinformationen. Bei den Einkaufsinformationen, die mehrere Preisinformationen haben, soll jeweils der erste Datensatz genommen werden wenn die Tabelle nach "min_discount_quantity" sortiert ist (der erste Datensatz ist dann immer auf die Bestellmenge "1" bezogen). Dieser Preis muss ausserdem noch durch "price_related_quantity" dividiert werden (hier steht eine "100" drin wenn 100 Stk. 10 Euro kosten, in "price" steht dann 10 Euro drin).

    Die erste Abfrage sollte eigentlich nicht so kompliziert sein denke ich. Die zweite hats aber ganz schön in sich ;-) Die erste ist aber auch viel wichtiger...

    Ich wäre sehr froh wenn mir hier jemand helfen könnte. Vielen Dank schonmal im Voraus!

    Grüsse
    neuling88
     
  2. akretschmer

    akretschmer Datenbank-Guru

    Grob gesehen ist das doch okay, oder? Kommt was falsches raus?

    Andreas
     
  3. neuling88

    neuling88 Benutzer

    Die Abfrage liefert zu viele Ergebnisse. So wie es aussieht werden alle Artikel zurückgegeben, die mindestens eine Einkaufsinformation ohne Preisinformationen haben. Also wenn ein Artikel eine Einkaufsinformation mit Preisen, und eine Einkaufsinformation ohne Preise hat, wird sie trotzdem zurückgegeben.

    mfg
     
  4. akretschmer

    akretschmer Datenbank-Guru

    Ähm, okay. Bilde die distinkte Menge der Artikel, die eine Preisinfo haben und ziehe diese Menge von der gesammten Artikelmenge ab.

    Andreas
     
  5. neuling88

    neuling88 Benutzer

    Hmm damit kann ich leider nicht viel anfangen... Könntest du mir da ein bisschen auf die Sprünge helfen?
    Ich kenne mich leider einfach zu wenig mit Datenbanken aus, lerne zwar immer wieder Neues dazu, aber das dauert halt seine Zeit (ziemlich umfangreiches Thema...) ;-)
     
  6. akretschmer

    akretschmer Datenbank-Guru

    Angenommen, du hast:

    Code:
    test=*# select * from parts ;
     id
    ----
      1
      2
      3
    (3 rows)
    
    Time: 0,164 ms
    test=*# select * from orderdetails ;
     id | part_id
    ----+---------
      1 |       1
      2 |       1
      3 |       2
    (3 rows)
    
    Time: 0,184 ms
    test=*# select * from pricedetails ;
     id | orderdetails_id
    ----+-----------------
      1 |               1
    (1 row)
    
    Du suchst also parts.id 2 und 3, richtig?

    Code:
    test=*# select distinct part_id, p.* from orderdetails o left join pricedetails p on o.id=p.orderdetails_id where p.id is not null;
     part_id | id | orderdetails_id
    ---------+----+-----------------
           1 |  1 |               1
    (1 row)
    
    Du hast also nur für part_id = 1 einen Preis.

    Code:
    test=*# select * from parts where id not in (select distinct part_id from orderdetails o left join pricedetails p on o.id=p.orderdetails_id where p.id is not null);
     id
    ----
      2
      3
    (2 rows)
    
    So vielleicht, oder hab ich Dich falsch verstanden?

    Andreas
     
  7. neuling88

    neuling88 Benutzer

    Jaa, genau das habe ich gesucht! Scheint so zu funktionieren wie ich mir das vorstelle, vielen Dank! :)

    Meinst du, meine zweite Frage wäre auch noch irgendwie lösbar?
    Momentan mache ich das noch mit PHP, doch das dauert schon sehr lange (zu lange...).
    Ist sicher nicht einfach, aber ich vermute mal, möglich sollte es schon irgendwie sein... ;-)

    mfg
     
  8. akretschmer

    akretschmer Datenbank-Guru

    Ja, da geb ichDir Recht, die ist auch lösbar ;-)

    Andreas
     
  9. akretschmer

    akretschmer Datenbank-Guru

    Deinen Satz "Bei den Einkaufsinformationen, die mehrere Preisinformationen haben, soll jeweils der erste Datensatz genommen werden wenn die Tabelle nach "min_discount_quantity" sortiert ist (der erste Datensatz ist dann immer auf die Bestellmenge "1" bezogen)." versteh nicht ganz, für den Rest mal diese Überlegung:

    Code:
    test=*# select * from parts ;
     id | instock
    ----+---------
      1 |      10
      2 |      20
      3 |      30
    (3 rows)
    
    test=*# select * from orderdetails ;
     id | part_id
    ----+---------
      1 |       1
      2 |       2
      3 |       3
    (3 rows)
    
    test=*# select * from pricedetails ;
     id | orderdetails_id | price | min_discount_quantity | price_related_quantity
    ----+-----------------+-------+-----------------------+------------------------
      1 |               1 |    12 |                     0 |                     10
      2 |               1 |    13 |                     0 |                     10
      3 |               2 |    20 |                     0 |                    100
    (3 rows)
    
    Nun mal die Preise berechnen:

    Code:
    test=*# select pa.id, avg((p.price / p.price_related_quantity::numeric)::numeric(5,2)) as part_price  from pricedetails p left join orderdetails o on p.orderdetails_id=o.id left join parts pa on o.part_id=pa.id group by pa.id;
     id |       part_price
    ----+------------------------
      1 | 1.25000000000000000000
      2 | 0.20000000000000000000
    (2 rows)
    
    Da hast Deine durchschnittlichen Stückpreise je ID aus parts. Das nun mit parts zu JOINen und die 2 Werte zu multiplizieren überlasse ich Dir zur Übung. Da ich mit INT gearbeitet hatte mußte ich a bissl CASTen, damit es nicht zu grob gerundet wird.

    Andreas
     
  10. neuling88

    neuling88 Benutzer

    Stimmt, war ein bisschen unglücklich formuliert. Einfacher wäre gewesen: Wenn es Einträge in der "pricedetails" gibt, dann soll für die Berechnung des Gesamtpreises immer nur jeweils diejenige Preisinformation genommen werden, in der "min_discount_quantity" == "1" ist. Das ist nämlich der Preis für eine Bestellmenge von 1Stk. (also ohne Mengenrabatt), die MUSS immer vorhanden sein, sofern es Preisinformationen gibt.

    Super, vielen Dank! :)
    Den Rest konnte ich selber noch einbauen, es scheint soweit auch zu funktionieren (und zwar wie erhofft viel schneller als vorher mit PHP). Das Ergebnis sieht nun folgendermassen aus:

    Code:
    SELECT SUM(part_price) AS price_sum
    FROM (SELECT parts.id, AVG(pricedetails.price * parts.instock / pricedetails.price_related_quantity) AS part_price
    FROM pricedetails LEFT JOIN orderdetails ON pricedetails.orderdetails_id=orderdetails.id
    LEFT JOIN parts ON orderdetails.part_id=parts.id
    WHERE pricedetails.min_discount_quantity=1
    GROUP BY parts.id) part_price
    
    Herzlichen Dank für deine Hilfe!

    Grüsse
    neuling88
     
Die Seite wird geladen...

Diese Seite empfehlen

  1. Diese Seite verwendet Cookies. Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.
    Information ausblenden