Abfrage über mehrere Tabellen für einen Bericht erstellen

[Geotrans]

ich möchte einen Bericht erstellen in dem sämtliche Werte die einer ("factory"."ID") (siehe Beziehungen.png) zugeordnet sind aufgelistet werden. Ich bekomme aber keine Abfrage erstellt in der ich alle Tabellen zusammenfasse ohne das Werte doppelt aufgeführt werden (siehe Abfrage.png - entspricht "Abfrage_Factory2" in "Datenbank1.odb").
Dazu habe ich folgende Fragen:
1.) Ist es überhaupt möglich bei dieser Datenbankstruktur einen solchen Bericht zu erstellen?
2.) wenn ja, wie muss dann die passende Abfrage dazu aussehen?

Als Anfänger würde ich mich über detaillierte Lösungsansätze die sich vorzugsweise über das GUI umsetzen lassen sehr freuen!

[RobertG]

Hallo Geotrans,

es müssen bei so einer Struktur zwangsläufig doppelte oder auch mehrfache Datensätze entstehen. Du gehst ja schließlich von der Tabelle "factory" aus und hast dort einen Datensatz dieser Tabelle mit 1:n- Verbindungen zu mehreren anderen Tabellen verknüpft. Kommt also die ID in der Tabelle InputGood zweimal vor, so gibt das in der Zusammenschau auf jeden Fall zwei Datensätze. Gleiches gilt dann für die anderen Tabellen aus. Eine zusammenfassende Abfrage müsste so aussehen:

Code: Alles auswählen

SELECT "factory".*, "factory_fields".*, "field".*, "InputGood".*, "OutputGood".*, "good".* 
FROM "factory" 
LEFT JOIN "factory_fields" ON "factory_fields"."factory_ID" = "factory"."ID"
LEFT JOIN "field" ON "factory_fields"."fields_ID" = "field"."ID" 
LEFT JOIN "InputGood" ON "InputGood"."factory_ID" = "factory"."ID"
LEFT JOIN "OutputGood" ON "OutputGood"."factory_ID" = "factory"."ID"
LEFT JOIN "good" ON "InputGood"."Good_ID" = "good"."ID" AND "OutputGood"."Good_ID" = "good"."ID"

Die Tabelle "erweiterungen" sowie den Anhang "parameter" habe ich jetzt einmal ausgelassen.
Klicke einfach auf "Abfrage in der SQL-Ansicht erstellen und füge den Code ein - alle Datensätze der Tabellen werden angezeigt, die irgendwie mit der Tabelle factory zusammenhängen. Nur die Datensätze, bei denen der Fremdschlüssel zu "factory" leer ist, werden nicht angezeigt.
Doppler bekommst Du im Bericht über die Gruppierungsmöglichkeit heraus.

Gruß

Robert

[Geotrans]

Vielen Dank für die schnelle Hilfe!
ist es richtig, das die letzten beiden Spalten bei der so erzeugten Abfrage leer bleiben? (siehe Abfrage2.png).
Was ist der Unterschied zu meiner Abfrage in Bezug auf den zu erstellenden Bericht?

[juetho]

ist es richtig, das die letzten beiden Spalten bei der so erzeugten Abfrage leer bleiben? (siehe Abfrage2.png).

Robert hat zunächst einmal alle Spalten für alle beteiligten Tabellen anzeigen lassen (jeweils .* für jeden Tabellennamen), damit man einen Überblick darüber erhält, welche Datensätze vorhanden sind und wie sie zusammenhängen. Die Verknüpfung für die Tabelle good sieht so aus (deine Grafik Beziehungen.png), dass ein Eintrag sowohl zu InputGood als auch zu OutputGood gehört. Das hat Robert in eine Verknüpfungsbedingung mit AND übersetzt:

Code: Alles auswählen

LEFT JOIN "good" ON "InputGood"."Good_ID" = "good"."ID" AND "OutputGood"."Good_ID" = "good"."ID"

Sofern dieses "sowohl als auch" nicht den Tatsachen entspricht, sollte das AND vielleicht durch OR ersetzt werden.

Was ist der Unterschied zu meiner Abfrage in Bezug auf den zu erstellenden Bericht?

Wenn ich dich richtig verstehe (Eingangsfrage), beziehst du dich auf Abfrage_Factory2. Der wesentlichste Unterschied liegt darin, dass Robert direkt die Tabellen verwendet, während du vier Abfragen "dazwischen geschaltet" hast und diese verknüpfst. (Ein weiterer Unterschied liegt darin, dass Robert .* verwendet hat. Damit tauchen ständig die IDs der einzelnen Tabellen auf; aber das ist nebensächlich.)

Wenn das gewünschte Ergebnis vollständig und sauber vorbereitet ist, ist dein Weg über mehrere Abfragen (VIEWs) sinnvoll. Das gilt vor allem dann, wenn durch Gruppierung und Filter auf bestimmte Felder der "fertigen Abfrage" zugegriffen werden soll. Solange das Ergebnis noch nicht den eigentlichen Zielen entspricht, ist der Weg über die einzelnen Tabellen sinnvoller - vor allem für Außenstehende (wie uns Helfer).

Ich bekomme aber keine Abfrage erstellt in der ich alle Tabellen zusammenfasse ohne das Werte doppelt aufgeführt werden (siehe Abfrage.png - entspricht "Abfrage_Factory2" in "Datenbank1.odb").

Welche "Doppelungen" stören dich eigentlich? Die 6 Zeilen für die factory_ID 13? Die unterscheiden sich schließlich durch good_ID und Parameter. Das sollte (wie Robert sagte) durch Gruppierungen im Bericht gesteuert werden.

Gruß Jürgen

Nachtrag: Was bedeutet eigentlich das OJ (zusammen mit den geschweiften Klammern) in den Abfragen? Das habe ich noch nie gesehen.

Code: Alles auswählen

FROM { OJ "factory" 
LEFT OUTER JOIN "Abfrage_Inputgood" ON "factory"."ID" = "Abfrage_Inputgood"."factory_ID" }

[Geotrans]

Sofern dieses "sowohl als auch" nicht den Tatsachen entspricht, sollte das AND vielleicht durch OR ersetzt werden.

OR brachte das gewünschte Ergebnis!

Welche "Doppelungen" stören dich eigentlich?

stören tun die mich eigentlich gar nicht, aber irgendwie bekomme ich meinen Bericht einfach nicht richtig gruppiert / formatiert. Ist mein erstes Datenbankprojekt. Deshalb wollte ich sicher gehen das meine Abfrage korrekt ist damit ich nicht an der falschen stelle den Fehler suche.
Habe jetzt den Code von RobertG folgendermaßen erweitert:

Code: Alles auswählen

SELECT "factory".*, "factory_fields".*, "field".*, "InputGood".*, "OutputGood".*, "good".*, "erweiterungen".*, "parameter".*
FROM "factory"
LEFT JOIN "factory_fields" ON "factory_fields"."factory_ID" = "factory"."ID"
LEFT JOIN "field" ON "factory_fields"."fields_ID" = "field"."ID"
LEFT JOIN "InputGood" ON "InputGood"."factory_ID" = "factory"."ID"
LEFT JOIN "OutputGood" ON "OutputGood"."factory_ID" = "factory"."ID"
LEFT JOIN "good" ON "InputGood"."Good_ID" = "good"."ID" OR "OutputGood"."Good_ID" = "good"."ID"
LEFT JOIN "erweiterungen" ON "erweiterungen"."factory_ID" = "factory"."ID"
LEFT JOIN "parameter" ON "erweiterungen"."parameter_ID" = "parameter"."ID"

Jetzt stellt sich natürlich die Frage nach welchen Feldern ich im Bericht gruppieren muss und in welcher Reihenfolge.
Wenn man einen Bericht mit dem Assistenten erstellt sind max. 4 Gruppierungen möglich. Gilt das auch ohne Assistent? (ich denke ich benötige Gruppierungen für factory, InputGood, OutputGood, factory-fields, und erweiterungen)

Was bedeutet eigentlich das OJ (zusammen mit den geschweiften Klammern) in den Abfragen?

keine Ahnung, habe die Abfragen mit dem GUI erstellt.
Ganz Herzlichen Dank für Eure Hilfe!!!

[juetho]

Gut, dass das geklärt ist:

Sofern dieses "sowohl als auch" nicht den Tatsachen entspricht, sollte das AND vielleicht durch OR ersetzt werden.

OR brachte das gewünschte Ergebnis!

Welche "Doppelungen" stören dich eigentlich?

stören tun die mich eigentlich gar nicht, aber irgendwie bekomme ich meinen Bericht einfach nicht richtig gruppiert / formatiert. Ist mein erstes Datenbankprojekt. Deshalb wollte ich sicher gehen das meine Abfrage korrekt ist damit ich nicht an der falschen stelle den Fehler suche.

Naja, das kannst eigentlich nur du beurteilen. Wir können ohne sehr genaue Beschreibung nicht erkennen, ob das Ergebnis deinen Erwartungen entspricht.

Jetzt stellt sich natürlich die Frage nach welchen Feldern ich im Bericht gruppieren muss und in welcher Reihenfolge... (ich denke ich benötige Gruppierungen für factory, InputGood, OutputGood, factory-fields, und erweiterungen)

Auch das kannst nur du beurteilen; es hängt natürlich davon ab, wie das Ergebnis aussehen soll.

Wenn man einen Bericht mit dem Assistenten erstellt sind max. 4 Gruppierungen möglich. Gilt das auch ohne Assistent?

Dazu kann ich nichts sagen. In den Einstellungen des Detailbereichs "Ausdruck für bedingte Anzeige" könnte man theoretisch eine weitere Gruppierung simulieren. Das dürfte aber so komplex werden, dass ich das keinesfalls versuchen würde. Theoretisch kann man auch die gezippte odb-Datei manuell bearbeiten - die eingebettete Datei reports/objnn/content.xml. Aber auch das geht nur für sehr versierte Anwender, die den internen Aufbau einer odb-Datei genauestens kennen.

Was bedeutet eigentlich das OJ (zusammen mit den geschweiften Klammern) in den Abfragen?

keine Ahnung, habe die Abfragen mit dem GUI erstellt.

Bei mir kommt das nicht. @Robert: Ist das eine Spezialität einer bestimmten OO-Version?

Gruß Jürgen

[Geotrans]

Das gewünschte Endergebnis meines Berichts soll so aussehen (für die ersten beiden Datensätze aus meinem Formular, "factory"."ID" 13+14) :
factory_name=neu1
DistributionWeight=1
Productivity=600
Range=200
electricity_boost=700
passenger_boost=200
mail_boost=100
copyright=Geotrans
Location=Land
MapColor=95
OutputGood[0]=Dachziegel
OutputCapacity[0]=800
OutputFactor[0]=80
InputGood[0]=Cement
InputFactor[0]=100
InputCapacity[0]=1000
InputSupplier[0]=20
InputGood[1]=Diesel
InputFactor[1]=50
InputCapacity[1]=60
InputSupplier[1]=3
BackImage[0][0][0][0][0][0]=alm.0.0
BackImage[0][0][1][0][0][0]=alm.0.1
BackImage[0][0][2][0][0][0]=alm.0.2
BackImage[0][1][0][0][0][0]=alm.0.3
BackImage[0][1][1][0][0][0]=alm.0.4
BackImage[0][1][2][0][0][0]=alm.0.5
BackImage[0][2][0][0][0][0]=alm.1.0
BackImage[0][2][1][0][0][0]=alm.1.1
BackImage[0][2][2][0][0][0]=alm.1.2
BackImage[1][0][0][0][0][0]=alm.1.3
BackImage[1][0][1][0][0][0]=alm.1.4
BackImage[1][0][2][0][0][0]=alm.1.5
BackImage[1][1][0][0][0][0]=alm.2.0
BackImage[1][1][1][0][0][0]=alm.2.1
BackImage[1][1][2][0][0][0]=alm.2.2
BackImage[1][2][0][0][0][0]=alm.2.3
BackImage[1][2][1][0][0][0]=alm.2.4
BackImage[1][2][2][0][0][0]=alm.2.5
BackImage[2][0][0][0][0][0]=alm.3.0
BackImage[2][0][1][0][0][0]=alm.3.1
BackImage[2][0][2][0][0][0]=alm.3.2
BackImage[2][1][0][0][0][0]=alm.3.3
BackImage[2][1][1][0][0][0]=alm.3.4
BackImage[2][1][2][0][0][0]=alm.3.5
BackImage[2][2][0][0][0][0]=alm.4.0
BackImage[2][2][1][0][0][0]=alm.4.1
BackImage[2][2][2][0][0][0]=alm.4.2
BackImage[3][0][0][0][0][0]=alm.4.3
BackImage[3][0][1][0][0][0]=alm.4.4
BackImage[3][0][2][0][0][0]=alm.4.5
BackImage[3][1][0][0][0][0]=alm.5.0
BackImage[3][1][1][0][0][0]=alm.5.1
BackImage[3][1][2][0][0][0]=alm.5.2
BackImage[3][2][0][0][0][0]=alm.5.3
BackImage[3][2][1][0][0][0]=alm.5.4
BackImage[3][2][2][0][0][0]=alm.5.5

factory_name=neu2
DistributionWeight=6
Productivity=600
Range=150
electricity_boost=900
passenger_boost=500
mail_boost=500
max_fields=20
min_fields=5
fields[0]=Gemüse
production_per_field[0]=300
spawn_weight[0]=60
probability_to_spawn[0]=70
fields[1]=Hopfenfeld
production_per_field[1]=400
spawn_weight[1]=40
probability_to_spawn[1]=80
OutputGood[0]=Aepfel
OutputCapacity[0]=1000
OutputFactor[0]=60
OutputGood[1]=Birnen
OutputCapacity[1=2000
OutputFactor[1]=50
OutputGood[2]=Bier
OutputCapacity[2]=3000
OutputFactor[2]=100
OutputGood[3]=Diesel
OutputCapacity[3]=200
OutputFactor[3]=100
OutputGood[4]=Cement
OutputCapacity[4]=1000
OutputFactor[4]=500

Die Werte in den eckigen Klammern müssten durch einen Zähler ermittelt werden. Ich habe aber keine Idee (mehr) nach welchen Feldern ich Gruppieren muss um auch nur annähernd eine solche Zusammenfassung zu bekommen, bzw. ob überhaupt meine Abfrage dafür richtig ist.

[RobertG]

Hallo Geotrans,

Kurz zum Bericht:

Wenn ich lese, dass der Bericht maximal nach 4 Gebieten gruppieren kann, dann versuchst Du das Ganze vermutlich mit dem (alten) Berichtserstellungsmodul. Das ist immer aktiv, wenn der Report-Generator nicht installiert wurde.
Zum Gruppieren und nachherigen Bearbeiten eignet sich der Report-Generator deutlich besser. Bei dem kannst Du in eine Gruppierung auch gleich mehrere Felder aufnehmen, die z.B. immer zu einem Datensatz einer Tabelle gehören.

Mit welchem System arbeitest Du?

Gruß

Robert

[Geotrans]

Opensuse 12.1
LibreOffice 3.5:build-413
Build-ID: 350m1(Build:413)

wenn ich "Bericht in Entwurfsansicht erstellen..." auswähle kann ich mehr Gruppierungen erstellen. Wenn ich "Bericht unter Verwendung des Assistenten" auswähle dann gibt es nur 4, die ich dann nach verlassen des Assistenten auch auf mehr erweitern. Ich war mir nur nicht ganz sicher ob das dann auch wirklich funktioniert.

[RobertG]

Hallo Geotrans,

ich habe erst noch einmal eine verbesserte Abfrage, da die letzten Spalten frei blieben:

Code: Alles auswählen

SELECT "factory".*, "factory_fields".*, "field".*, "InputGood".*, "OutputGood".*, "good".*, "good2".*, "erweiterungen".*, "parameter".* 
FROM "factory" 
LEFT JOIN "factory_fields" ON "factory_fields"."factory_ID" = "factory"."ID" 
LEFT JOIN "field" ON "factory_fields"."fields_ID" = "field"."ID" 
LEFT JOIN "InputGood" ON "InputGood"."factory_ID" = "factory"."ID" 
LEFT JOIN "OutputGood" ON "OutputGood"."factory_ID" = "factory"."ID" 
LEFT JOIN "good" ON "InputGood"."Good_ID" = "good"."ID" 
LEFT JOIN "good" AS "good2" ON "OutputGood"."Good_ID" = "good2"."ID" 
LEFT JOIN "erweiterungen" ON "erweiterungen"."factory_ID" = "factory"."ID" 
LEFT JOIN "parameter" ON "erweiterungen"."parameter_ID" = "parameter"."ID"

Mit der anderen Abfrage kamen die beiden "good"-Namenszuordnungen nicht zu Rande.

Mit dem Bericht gestaltet sich das sicher äußerst schwierig - was aber an der Struktur Deiner Datensammlung liegt.
Grundsätzliches zum Bericht: Alle Felder, die Du mit [Index] versiehst, müssten eigentlich in einer Untergruppe erscheinen. Normalerweise muss die Struktur so laufen, dass in dem Detailbereich die meisten Doppler Deiner Auflistungen liegen müssen. Sehe ich allerdings die erste gewünschte Auflistung, so müsste da dann z.B. alles ohne Zusatznummer in die erste Gruppe "factory_name", alle zu "Output ..." bildet eine zweite Gruppe und dann wirds problematisch: "Input ..." müsste die nächste Untergruppe sein. Die Reihenfolge wie bei Dir angegeben wirst Du nie erreichen, da die Image-Dateien alle in einem Feld enthalten sind ...

Zu "BackImage..." fällt mir noch auf, dass da einmal gar nichts drin ist, ein anderes Mal ein ganz langer Strang. Einer der besonderen Nachteile des Report-Builders ist, dass die darin enthaltenen Textfelder nicht automatisch ihre Größe anpassen können. Du musst also ein Feld mit genügend Platz für den größten Wert bereitstellen - und das wird in diesem Fall eindutig sehr groß und erscheint in der nächsten Gruppe als freier Bereich.

Gruß

Robert

[RobertG]

@Jürgen,

die Formulierungen zum {OJ ...} produziert tatsächlich die GUI - auch in LO, wenn Du zumindest mit Base und der internen HSQLDB arbeitest. Du musst dazu die Verbindungslinie zwischen den beiden Tabellen mit einem rechten Mausklick bearbeiten und auf einen linken oder rechten Verbund einstellen. Mit OJ beginnend soll das Ganze wohl eine Kompatibilität mit ODBC-Verbindungen herstellen, sofern ich z.B. das MySQL-Handbuch richtig deute.

Gruß

Robert

[juetho]

Du musst dazu die Verbindungslinie zwischen den beiden Tabellen mit einem rechten Mausklick bearbeiten und auf einen linken oder rechten Verbund einstellen. Mit OJ beginnend soll das Ganze wohl eine Kompatibilität mit ODBC-Verbindungen herstellen, sofern ich z.B. das MySQL-Handbuch richtig deute.

Aha, dann nehme ich das einfach so hin. Und weil ich solche Sachen direkt mit SQL und nicht mit der GUI erledige, ist es auch kein Wunder, dass mir das noch nie begegnet ist. Gruß Jürgen

[Geotrans]

Hallo Robert,
Vielen Dank für Deine unermüdliche Hilfe!

Mit dem Bericht gestaltet sich das sicher äußerst schwierig - was aber an der Struktur Deiner Datensammlung liegt.

Wie würde man denn sinnvollerweise die Datensammlung strukturieren? Wichtig für die Daten ist ja nur das sie der jeweiligen factory zugeordnet werden und das die werte für die Felder/OuputGood/InputGood zusammenbleiben.

Die Reihenfolge wie bei Dir angegeben wirst Du nie erreichen, da die Image-Dateien alle in einem Feld enthalten sind ...

Die Reihenfolge ist nicht unbedingt so vorgeschrieben, war halt nur so wie ich sie gewohnt bin.

Zu "BackImage..." fällt mir noch auf, dass da einmal gar nichts drin ist, ein anderes Mal ein ganz langer Strang. Einer der besonderen Nachteile des Report-Builders ist, dass die darin enthaltenen Textfelder nicht automatisch ihre Größe anpassen können. Du musst also ein Feld mit genügend Platz für den größten Wert bereitstellen - und das wird in diesem Fall eindutig sehr groß und erscheint in der nächsten Gruppe als freier Bereich.

Da es sich hier noch um eine Datenbank im Versuchsstadium handelt habe ich noch nicht in alle Felder bei BackImage was eingetragen. Später wird bei BackImage immer ein Eintrag sein.Allerdings in sehr Unterschiedlichem Umfang, von einer Zeile bis noch größer wie im oben genannten Beispiel.
Die reine Optik des Berichtes ist für mich übrigens zweitrangig, da ich den Inhalt anschließend in eine reine Textdatei kopiere, bei der ich dann auch durch Suchen und Ersetzen relativ einfach irgendwelche Leerzeilen raus bekomme.

Gruß,
Georg

[RobertG]

Hallo Geoorg,

Wie würde man denn sinnvollerweise die Datensammlung strukturieren? Wichtig für die Daten ist ja nur das sie der jeweiligen factory zugeordnet werden und das die werte für die Felder/OuputGood/InputGood zusammenbleiben.

Wie sollen diese Felder zusammen bleiben? Die Tabellen geben das nicht her. Du hast hier Werte aus 2 Tabellen, die außer der Beziehung zu factory keine Verbindung haben.
Folgende Konstruktionen sind in Berichten einfach zu bewerkstelligen:
Tabelle 1 hat den Fremdschlüssel in Tabelle 2, die wiederum den Fremdschlüssel in Tabelle 3 hat usw ... - also strikt hierarchisch geordnet. Tabelle 1 würde dann mit dem Primärschlüssel die oberste Gruppe darstellen, Tabelle 2 die nächste Kategorie usw.
Bei Deinen Tabellen kannst Du lediglich Tabelle 1 bestimmen: factory. Alles was in "factory" auftaucht gehört in die Gruppierung. Alle anderen Werte gehören in den Bereich "Detail", da sie außer der Verbindung zu "factory" nichts miteinander zu tun haben.

Die reine Optik des Berichtes ist für mich übrigens zweitrangig, da ich den Inhalt anschließend in eine reine Textdatei kopiere, bei der ich dann auch durch Suchen und Ersetzen relativ einfach irgendwelche Leerzeilen raus bekomme.

Dann bietet es sich vielleicht an, das Ganze nicht als eine Writer-Datei mit dem Report-Builder zu erstellen. Der macht auch Tabellendokumente. Die haben dann keine Formatierungen. In den Writer-Dateien hingegen wirst Du auf lauter Rahmen stoßen, die eine Abspeicherung als reine Textdatei behindern.

Gruß

Robert

[Geotrans]

In dem Base Handbuch http://wiki.documentfoundation.org/imag ... te_V35.pdf ist die Adresse durch eine Abfrage bereits zu einem Block zusammengefasst. Wäre das vielleicht die Lösung, das ich jeweils aus den Tabellen factory_fields, InputGoods, OutputGoods und Erweiterungen die Wert in einem solchen Block zusammen zu fassen?