Datenbankabfrage über 3 Tabellen

[neuling88]

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:

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

 

[akretschmer]

Mein erster Ansatz sieht so aus, da fehlt aber wohl noch ein bisschen was:

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

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

Andreas

 

[neuling88]

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

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

 

[akretschmer]

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

Andreas

 

[neuling88]

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

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...) ;-)

 

[akretschmer]

Angenommen, du hast:

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?

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.

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

 

[neuling88]

So vielleicht, oder hab ich Dich falsch verstanden?

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

 

[akretschmer]

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

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

Andreas

 

[akretschmer]

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:

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:

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

 

[neuling88]

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,

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.

Nun mal die Preise berechnen:

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;

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:

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