Abfrage in Firebird DB über 6 Tabellen

[daniel775]

Hallo,


ich verzweifel gerade an einer Abfrage bzw. an dem Konstrukt der Tabellen in Firebird.

Also die Tabellen müssen folgende Anforderungen erfüllen:
- Ein Artikel muss mehreren Lieferanten zuzuordnen sein.
- Jeder Artikel kann eine fremde Artikel-Nr eines Lieferanten haben.
- Jeder Artikel hat unterschiedliche Einkaufspreise von jeden Lieferanten mit unterschiedlichen Zeiträumen.


Auszug aus dem Aufbau der Tabellen:
(Siehe Anhang)


SQL-Query

Code:

SELECT k.Firmenname, k.KREDITORNR, k1.Datum, pr.LISTENPREIS_NETTO, pr.EKPREIS_NETTO
FROM 
KREDITOREN as k,
KREDITOR_PREISE as pr,
REL_KREDITOR_ARTIKEL as r,
REL_KRED_ART_PREISE as rp
JOIN KREDITOREN on k.ID = r.KREDITOR_ID
JOIN REL_KREDITOR_ARTIKEL on r.id = rp.KREDITOR_ART_ID
JOIN REL_KRED_ART_PREISE on rp.KREDITOR_PREISE_ID = pr.id
JOIN (SELECT k.id, MAX(pr.SDATETIME) as Datum
FROM
KREDITOREN as k,
REL_KREDITOR_ARTIKEL as r,
KREDITOR_PREISE as pr 
JOIN KREDITOREN on k.ID = r.KREDITOR_ID
JOIN REL_KREDITOR_ARTIKEL on r.KREDITOR_ART_ID = pr.KREDITOR_ART_ID
GROUP BY k.id
) k1
on k1.id = k.id
GROUP BY k.Firmenname, k.KREDITORNR, k1.Datum, pr.LISTENPREIS_NETTO, pr.EKPREIS_NETTO
ORDER BY k1.Datum;

Ergebnis meiner Abfrage ist, das ich 4 Zeilen Ergebnis bekomme, obwohl ich nur 2 Erwarte mit
jeweils eine Zeile und den aktuellen Preisen der beiden Lieferanten.

Wobei das Datum von dem Datensatz der eigentlich nicht im Ergebnis sein soll, das Datum vom Datensatz übernommen hat, das durch "JOIN (SELECT k.id, MAX(pr.SDATETIME) as Datum" als größten Datensatz selektiert wurde.

Die Tabelle "Kreditor_Preise" hat 4 Zeilen. Jeder Lieferant der beiden hat zwei Zeilen. Wobei jede Zeile ein unterschiedliches Datum hat. Also das Ergebnis soll sein 2 Zeilen, 2 Lieferanten, 2 aktuelle Preise. Also mit dem ältesten Datum.

Also zurück zu meinen eigentlichen Frage. Wie erreiche ich bei einer gruppierten Abfrage das nur der Datensatz mit dem ältesten Datum in der Ergebnismenge ist?
Wie muss ich die Tabellen umdesignen um die o.g. anfoderungen zu erfüllen?


Beispiel:
ID Datum Listenpreis Einkaufspreis
1 05.05.2017 699,00 492,70
2 13.08.2017 599,00 392,70
3 25.05.2017 749,00 654,99
4 05.09.2017 799,00 699,99


Über einen Lösungsansatz über den ich auch nachgedacht habe wäre folgender:

D Datum Listenpreis Einkaufspreis Aktiv
1 05.05.2017 699,00 492,70 0
2 13.08.2017 599,00 392,70 1
3 25.05.2017 749,00 654,99 0
4 05.09.2017 799,00 699,99 1

Halt mit einen zusätzlichen Datenfeld "Aktiv" :coder2:


Danke für das Lesen, hoffe auf zahlreiche-/hilfreiche Antworten :lol:

[Neumann]

Das ganze sieht für mich - der weder alle Tabellenstrukturen kennt noch genügend Beispieldaten hat - erstmal etwas undurchsichtig aus.

Man könnte es mit 2 Ansätzen (auch kombinierbar) vereinfachen:

1. Mit Views arbeiten, die vereinfachte Zwischentabellen für die Abfrage liefern.

2. Mit einer Stored Procedure arbeiten.

Habe auch schon das Problem gehabt, dass zu viele Joins in einer Abfrage oft ein Ergebnis liefern, das man nicht erwartet oder haben will.

[hoika]

Hallo,
bei LaTex heißt es in solchen Fällen immer "Minimal-Beispiel",
also poste eine GDB an, die eine minimale Anzahl an Datensätzen enthält,
aber bei deiner Query falsche Ergebnisse liefert.

Dann noch mal:
Ist
Soll

[daniel775]

OK,

ich habe einen Auszug aus den Tabellen gebaut. Die DB befindet sich im Anhang. Das SQL Script zum extrahieren wollte leider nicht richtig funktionieren. Hänge es aber trotzdem mal an.


SQL Statement für die Abfrage:

SELECT k.Firmenname, k.KREDITORNR, k1.Datum, pr.LISTENPREIS_NETTO, pr.EKPREIS_NETTO
FROM
KREDITOREN as k,
KREDITOR_PREISE as pr,
REL_KREDITOR_ARTIKEL as r,
REL_KRED_ART_PREISE as rp
JOIN KREDITOREN on k.ID = r.KREDITOR_ID
JOIN REL_KREDITOR_ARTIKEL on r.id = rp.KREDITOR_ART_ID
JOIN Kreditor_Preise on rp.KREDITOR_PREISE_ID = pr.id
JOIN (SELECT k.id, MAX(pr.SDATETIME) as Datum
FROM
KREDITOREN as k,
REL_KREDITOR_ARTIKEL as r,
KREDITOR_PREISE as pr
JOIN KREDITOREN on k.ID = r.KREDITOR_ID
JOIN REL_KREDITOR_ARTIKEL on r.KREDITOR_ART_ID = pr.KREDITOR_ART_ID
GROUP BY k.id
) k1
on k1.id = k.id
GROUP BY k.Firmenname, k.KREDITORNR, k1.Datum, pr.LISTENPREIS_NETTO, pr.EKPREIS_NETTO
ORDER BY k1.Datum;

Komischerweise bekomme ich bekomme ich jetzt nur einen Lieferanten bei der Abfrage.

[hstreicher]

Ich würde erst mal die Doppelten Tabellen rauswerfen und dann entweder SQL 89 oder SQL 92 Syntax verwenden und nicht beides gleichzeitig

mfg Hannes

[p80286]

versuch es mal hiermit:

SQL-Code:

SELECT K.irgendwas,KP.Sdatetime
FROM 
  Kreditoren as k
    join Rel_Kreditor_Artikel KAr on k.ID=KAr.Kreditor_ID
    join Artikel              A   on A.ID=KAr.Art_ID
    join Kreditor_Artikel     KA  on KA.ID=KAr.Kreditor_Art_ID
    join Rel_Kred_Art_Preise  KAPr on KAPr.Kreditor_Art_ID=KA.ID
    Join Kreditor_Preise      KP  on KAPr.Kreditor_Preise_ID=KP.ID
where 1=1
  and max(KP.sDatetime) 
group by k.irgendwas

Gruß
K-H

[daniel775]

Also irgendetwas stimmt an dem Query nicht. Wenn ich "and max(KP.sDatetime)" weglasse, dann bekomme ich zumindest ein Ergebnis. Sonst die Errormessage
"Invalid token.
Dynamic SQL Error.
SQL error code = -104.
Token unknown - line 11, column 1.
Group."

[p80286]

wenn Du
"Group by k.irgendwas" genutzt hast, dann kann das nicht funktionieren, das ist der Platzhalter für Daten, die du aus der Kreditoren-Tabelle abfragen willst.

Gruß
K-H

[daniel775]

Irgendwie hab ich befürchtet das die Antwort kommt. Nein, hab ich natürlich nicht 1:1 genutzt!
sondern:

SELECT k.Firmenname, k.KREDITORNR, KP.datum, KP.LISTENPREIS_NETTO, KP.EKPREIS_NETTO
FROM
Kreditoren as k
join Rel_Kreditor_Artikel KAr on k.ID = KAr.Kreditor_ID
join Artikel A on A.ID = KAr.Art_ID
join Kreditor_Artikel KA on KA.ID = KAr.Kreditor_Art_ID
join Rel_Kred_Art_Preise KAPr on KAPr.Kreditor_Art_ID = KA.ID
join Kreditor_Preise KP on KAPr.Kreditor_Preise_ID = KP.ID
where 1=1
and max(KP.SDATETIME) as datum
group by k.FIRMENNAME, k.KREDITORNR, KP.LISTENPREIS_NETTO, KP.EKPREIS_NETTO;


oder


SELECT k.Firmenname, k.KREDITORNR, KP.SDATETIME, KP.LISTENPREIS_NETTO, KP.EKPREIS_NETTO
FROM
Kreditoren as k
join Rel_Kreditor_Artikel KAr on k.ID = KAr.Kreditor_ID
join Artikel A on A.ID = KAr.Art_ID
join Kreditor_Artikel KA on KA.ID = KAr.Kreditor_Art_ID
join Rel_Kred_Art_Preise KAPr on KAPr.Kreditor_Art_ID = KA.ID
join Kreditor_Preise KP on KAPr.Kreditor_Preise_ID = KP.ID
where 1=1
and max(KP.SDATETIME)
group by k.FIRMENNAME, k.KREDITORNR, KP.LISTENPREIS_NETTO, KP.EKPREIS_NETTO;


oder

SELECT k.Firmenname, k.KREDITORNR, KP.sdatetime, KP.LISTENPREIS_NETTO, KP.EKPREIS_NETTO
FROM
Kreditoren as k
join Rel_Kreditor_Artikel KAr on k.ID = KAr.Kreditor_ID
join Artikel A on A.ID = KAr.Art_ID
join Kreditor_Artikel KA on KA.ID = KAr.Kreditor_Art_ID
join Rel_Kred_Art_Preise KAPr on KAPr.Kreditor_Art_ID = KA.ID
join Kreditor_Preise KP on KAPr.Kreditor_Preise_ID = KP.ID
where 1=1
group by k.FIRMENNAME, k.KREDITORNR, KP.sdatetime, KP.LISTENPREIS_NETTO, KP.EKPREIS_NETTO;


wobei ich beim letzten ein Ergebnis habe. Aber eben ohne " and max(KP.sDatetime) "

[TBx]

SQL-Code:

where 1=1
  and max(KP.SDATETIME) as datum

sollte da mal korrigiert werden, die erste Zeile enthält eine überflüssige Bedingung, da immer erfüllt und die zweite Zeile enthält nur einen Ausdruck, keine Bedingung.

[daniel775]

OK, ihr habt mich geschafft. Ganz durcheinander!

[nahpets]

Mir ist zwar nicht klargeworden, was Du genau möchtest, aber eventuell kommt das ja Deinen Vorstellungen näher:

SQL-Code:

SELECT 
  k.Firmenname, 
  k.KREDITORNR, 
  KP.sdatetime, 
  KP.LISTENPREIS_NETTO, 
  KP.EKPREIS_NETTO, 
  max(KP.sDatetime) as Datum
FROM
  Kreditoren as k
  join Rel_Kreditor_Artikel KAr on k.ID = KAr.Kreditor_ID
  join Artikel A on A.ID = KAr.Art_ID
  join Kreditor_Artikel KA on KA.ID = KAr.Kreditor_Art_ID
  join Rel_Kred_Art_Preise KAPr on KAPr.Kreditor_Art_ID = KA.ID
  join Kreditor_Preise KP on KAPr.Kreditor_Preise_ID = KP.ID
group by 
  k.FIRMENNAME, 
  k.KREDITORNR, 
  KP.sdatetime, 
  KP.LISTENPREIS_NETTO, 
  KP.EKPREIS_NETTO;

[daniel775]

OK, ein Fehler war, das in der Tabelle "Rel_Kred_Art_Preise" die Datensätze für die Zuordnung fehlten.
Jetzt sieht das Ergebnis mit folgender Abfrage so aus:

SELECT k.Firmenname, k.KREDITORNR, KP.sdatetime, KP.LISTENPREIS_NETTO, KP.EKPREIS_NETTO
FROM
Kreditoren as k
join Rel_Kreditor_Artikel KAr on k.ID = KAr.Kreditor_ID
join Artikel A on A.ID = KAr.Art_ID
join Kreditor_Artikel KA on KA.ID = KAr.Kreditor_Art_ID
join Rel_Kred_Art_Preise KAPr on KAPr.Kreditor_Art_ID = KA.ID
join Kreditor_Preise KP on KAPr.Kreditor_Preise_ID = KP.ID
group by k.FIRMENNAME, k.KREDITORNR, KP.sdatetime, KP.LISTENPREIS_NETTO, KP.EKPREIS_NETTO;


Ergebnis SQL-Query siehe Anhang

[daniel775]

@ nahpets

Das Ergebnis ist (Siehe Anhang)



Gewünscht ist NUR Datensatz Nr. 2 und 4

[p80286]

Zitat:

Zitat von TBx (Beitrag 1380728)

SQL-Code:

where 1=1
  and max(KP.SDATETIME) as datum

sollte da mal korrigiert werden, die erste Zeile enthält eine überflüssige Bedingung, da immer erfüllt und die zweite Zeile enthält nur einen Ausdruck, keine Bedingung.

Oh pardon, da hab ich den typischen CopynPaste-Mist gebaut.
Das 1=1 oder 1=2 nutze ich um die angefragten Felder(Namen) zurück zu bekommen und um ggf. Konstrukte wie

Delphi-Quellcode:

 sqltxt.Add('  and Feld=wert ';
 sqltxt.Add('  and Person.sex=female';
 if irgendeinwert then
  sqltxt.Add('  and Feld2 is not null';

enzusetzen. Ist nur meine pers. Marotte.

Um die beiden Datensätze mit dem neuesten/jüngsten Datum zu erhalten mußt Du jetzt nur noch Deinen ursprünglichen Ansatz verfolgen:

SQL-Code:

  .....
  join (select k.id,max(KP.ABDatum) datum
FROM 
   Kreditoren as k
     join Rel_Kreditor_Artikel KAr on k.ID=KAr.Kreditor_ID
     join Kreditor_Artikel KA on KA.ID=KAr.Kreditor_Art_ID
     join Rel_Kred_Art_Preise KAPr on KAPr.Kreditor_Art_ID=KA.ID
     Join Kreditor_Preise KP on KAPr.Kreditor_Preise_ID=KP.ID
Group by k.ID   ) mindate on (mindate.id=k.id and mindate.datum=kp.sdatetime)

ggf. mußt Du noch die Aliase und das/die Datumsfelder anpassen, aber ich hoffe das Prinzip ist deutlich geworden.

Gruß
K-H

[daniel775]

TOP! :thumb: Herzlichen Dank.
Die komplette geglückte Abfrage sieht nun so aus:

Code:

SELECT 
  k.Firmenname, 
  k.KREDITORNR, 
  mindate.datum,
  KP.LISTENPREIS_NETTO, 
  KP.EKPREIS_NETTO,
  KP.RABATTWERT,
  KP.EKPREIS_NETTO_NETTO
FROM
  Kreditoren as k
  join Rel_Kreditor_Artikel KAr on k.ID = KAr.Kreditor_ID
  join Artikel A on A.ID = KAr.Art_ID
  join Kreditor_Artikel KA on KA.ID = KAr.Kreditor_Art_ID
  join Rel_Kred_Art_Preise KAPr on KAPr.Kreditor_Art_ID = KA.ID
  join Kreditor_Preise KP on KAPr.Kreditor_Preise_ID = KP.ID
  join (select k.id, max(KP.SDATETIME) as datum
  from
  Kreditoren as k
  join Rel_Kreditor_Artikel KAr on k.ID = KAr.Kreditor_ID
  join Kreditor_Artikel KA on KA.ID = KAr.Kreditor_Art_ID
  join Rel_Kred_Art_Preise KAPr on KAPr.Kreditor_Art_ID = KA.ID
  join Kreditor_Preise KP on KAPr.Kreditor_Preise_ID = KP.ID
group by k.id
) mindate 
on mindate.id=k.id and mindate.datum=KP.sdatetime
group by 
  k.FIRMENNAME, 
  k.KREDITORNR,  
  mindate.datum,
  KP.LISTENPREIS_NETTO, 
  KP.EKPREIS_NETTO,
  KP.RABATTWERT,
  KP.EKPREIS_NETTO_NETTO;