Tabellen bei Abfrage zusammenführen und div. Berechnungen

[gdconsult]

Hallo!

Ich habe gerade ein etwas komplexeres Problem, bei dem ich alleine leider nicht weiterkomme.

Generell muss ich 3 Tabellen in einer Abfrage zusammenführen wobei zwei der Tabellen nicht nur einen Wert enthalten können. Aber zuerst mal das Schema etwas vereinfacht:

Tabelle Dienstleister:

ID | Name | ...
1  | Test | ...

Tabelle Bewertungen

ID | DienstlID | Preis | Zuverlaessigkeit
1  | 1         | 5     | 3
2  | 1         | 4     | 3

Tabelle Preise

ID | DienstlID | Preis | Einheit | Menge
1  | 1         | 80    | 1h      | 10
2  | 1         | 90    | 1h      | 1
2  | 1         | 45    | 1/2h    | 1

Ich bräuchte nun eine Art SELECT ID, Name FROM Dienstleister
mit JOIN MAX(Preis) FROM Preise WHERE Einheit = "1h" ...
und JOIN (AVG(Preis) + AVG(Zuverlaessigkeit)) FROM Bewertungen

damit die Abfrage so in etwa folgendes liefert:

ID | Name | Preis | Bewertung
1  | Test | 90    | 7,5 (5+4 = 9/2 = 4,5 | 3+3 = 6 / 2 = 3 >> 4,5+3 = 7,5)

Wobei ich bei der Bewertung offen bin. Ich brauche es um wahlweise nach Preis/h, Bewertung, Name oder ID zu sortieren. Wichtig ist dabei, dass es fair bleibt. Also Jemand der 3 Bewertungen hat braucht und jemend der 1 oder 10 hat müssen miteinander vergleichbar sein - sprich eine Art Durchschnitt.

 

[akretschmer]

test=*# select * from dienstleister ;
 id | name
----+------
  1 | test
(1 Zeile)

test=*# select * from bewertungen ;
 id | dienstleister_id | preis | zuverlaessigkeit
----+------------------+-------+------------------
  1 |  1 |  5 |  3
  2 |  1 |  4 |  3
(2 Zeilen)

test=*# select * from preise;
 id | dienstleister_id | preis | einheit | menge
----+------------------+-------+---------+-------
  1 |  1 |  80 |  1 |  10
  2 |  1 |  90 |  1 |  1
  3 |  1 |  45 |  0.5 |  1
(3 Zeilen)

test=*# select d.id, d.name, p.preis, avg(b.preis) + avg(b.zuverlaessigkeit) as bewertung from bewertungen b  left join dienstleister d on b.dienstleister_id=d.id left join (select dienstleister_id, max(preis / einheit) as preis from preise group by dienstleister_id) p on d.id=p.dienstleister_id group by d.id, d.name, p.preis;
 id | name |  preis  |  bewertung   
----+------+---------------------+--------------------
  1 | test | 90.0000000000000000 | 7.5000000000000000
(1 Zeile)

test=*#

insert into akretschmer select * from kaffee;