SQL Abfrage über mehrere Tabellen

[Generic1]

Hallo,

ich habe in meiner Datenbank folgendes Schema:

Wenn jetzt in der Tabelle Collection ein Eintrag gemacht wird, dann möchte ich in einem Trigger eine SELECT Abfrage machen, das mir "Name und Version" von der Tabelle Package und die UniqueDeviceID von Device des aktuellen Collection- Eintrags zurückgibt.

Bzw. am Besten wäre -> alle Einträge in der Tabelle Collection nehmen und dann Name.Version und UniqueDeviceID zu den Einträgen in der Collection ermitteln.

Weiß jemand wie ich das am einfachsten mit einem SQL- Statement machen kann bzw. wies überhaupt geht? Ich habs schon eine weile probiert, es ist mir aber leider nicht gelungen.
Vielen Dank,
lg

 

[XHelp]

Suchst du einfach eine große Query mit Joins, oder besteht woanders ein Problem?

 

[Generic1]

Ich weiß nicht wie ich die Joins legen muss, damit ich mein gewünschtes Ergebnis bekomme.
Vielleicht kann jemand diese SELECT- Abfrage vom Stand aus hinschreiben - da wäre mir natürlich sehr geholfen.

 

[XHelp]

ohne das jetzt auszuprobieren:

SELECT `package`.`Name`, `package`.`Version` FROM `collection` 
INNER JOIN `packages` ON `collection`.`Package_ID` = `packages`.`ID` 
INNER JOIN `Device_Collection` ON `collection`.`ID`=`Device_Collection`.`Collection_ID` 
INNER JOIN `Devices` ON `Device_Collection`.`Device_ID`=`Devices`.`ID` 
WHERE `collection`.`ID`= NEW.ID

 

[Michael...]

Mal so grob runtergeschrieben:

Select Name, Version, UniqueDeviceID
From packages, collection, device_collection, devices
Where packages.ID = package_ID and collection.ID = collection_ID and device_ID = devices.ID

PS.: Es gibt auch Online Tutorials zu SQL

 

[Generic1]

vielen Dank, das hilft mir schon,
eine Bitte hab ich noch, ich hab mich leider vertan, das SELECT sollte von der Tabelle Device_Collection ausgehen.
Wäre Euch sehr dankbar wenn ihr mir hier noch mal helfen könntet.
vielen Dank!!!
lg

 

[XHelp]

eine Bitte hab ich noch, ich hab mich leider vertan, das SELECT sollte von der Tabelle Device_Collection ausgehen.

Macht ja nichts. Der Zusammenhang zwischen den Tabellen ist ja gleich, nur die Denkreihenfolge ist anders

 

[Generic1]

Das hat wunderbargeklappt, vielen Dank,
Ich hab jetzt noch eine Zusatzbedingung und zwar soll nur Name.Version und zugehörige UniqueDeviceID
von dem Tupel mit der höchsten Version selektiert werden, wenn ein Package- Name öfter als einmal vorkommt.
Geht das noch irgendwie?
vielen Dank,
lg

 

[Generic1]

Weiß jemand wie man das machen kann?

 

[andiv]

Wo bleibt deine Eigeninitiative?

um den Eintrag mit der höchsten Version zu bekommen verwendest du MAX(...).
Wenn du das nur dort anwenden willst, wo der Package-Name gleich ist, dann brauchst du halt noch ein GROUP BY(...).
Das musst du jetzt nur noch passend mit deinem bestehenden Query kombinieren.

 

[Generic1]

Wo bleibt deine Eigeninitiative?

um den Eintrag mit der höchsten Version zu bekommen verwendest du MAX(...).
Wenn du das nur dort anwenden willst, wo der Package-Name gleich ist, dann brauchst du halt noch ein GROUP BY(...).
Das musst du jetzt nur noch passend mit deinem bestehenden Query kombinieren.

Version ist ein varchar, da wird mir MAX(...) nicht viel auszurichten sein.
Bin in SQL nicht (mehr) so geübt, deshalb die Frage, bzw. mit GROUP BY hab ich bisnher noch nichts gemacht.

 

[Tomate_Salat]

Google?!

 

[Hrtgpdh]

Netter Link der die unterschiedlichen Join- Möglichkeiten anzeigt:

Mit JOIN zwei Tabellen zu einer virtuellen Tabelle kombinieren


Grüße

Hartwig

 

[andiv]

Version ist ein varchar, da wird mir MAX(...) nicht viel auszurichten sein.
Bin in SQL nicht (mehr) so geübt, deshalb die Frage, bzw. mit GROUP BY hab ich bisnher noch nichts gemacht.

Warum ist Version ein varchar? Ist das nicht ein kleiner Designfehler? Du kannst ja mal schauen, ob deine Datenbank soetwas wie CONVERT(spalte, typ) kennt, dann könntest du die Version von varchar nach bigint casten und dann MAX drauf anwenden. Vorausgesetzt natürlich, dass nur Zahlen in der Versionsspalte stehen.

Die andere Möglichkeit ist du liest einfach alle Einträge und suchst dir dann programmseitig die richtigen raus.

 

[Generic1]

Warum ist Version ein varchar? Ist das nicht ein kleiner Designfehler? Du kannst ja mal schauen, ob deine Datenbank soetwas wie CONVERT(spalte, typ) kennt, dann könntest du die Version von varchar nach bigint casten und dann MAX drauf anwenden. Vorausgesetzt natürlich, dass nur Zahlen in der Versionsspalte stehen.

Die andere Möglichkeit ist du liest einfach alle Einträge und suchst dir dann programmseitig die richtigen raus.

Die Version schaut so aus: 1.2.3.4.5.6.7 ... ist also eher ein varchar als eine Zahl.

 

[andiv]

Die Version schaut so aus: 1.2.3.4.5.6.7 ... ist also eher ein varchar als eine Zahl.

In dem Fall würde ich gar nicht länger versuchen mit SQL da was zu machen. Les einfach alle Einträge und such dir dann manuell die mit den höchsten Versionen raus. Das ist zwar sicher sehr umständlich, aber wer in einer Spalte keine atomaren Daten ablegt ist selbst schuld.

 

[Tomate_Salat]

ok, nette versionsnummer^^. Aber wenn man sich mal diese Vorlage zur generierung einer Versionsnummer nimmt, erkennt man schon: mit einem Feld vom Typ Zahl kommt man nicht weit. Es würde sich anbieten vllt die Versionsnummer zu splitten. Da kannst du dann nach Haupt/Nebenversion filtern.

MFG

Tomate_Salat

 

[Michael...]

Wenn man solche Versionsnummern vernünftig via PL/SQL handeln will, müsste man eigentlich die Tabelle normalisieren. Sonst tut man sich schwer aus Nummern wie 1.2.1.1 und 1.12.1.1 das Maximum zu ermitteln. Man könnte auch in einem zweiten Feld einen eindeutigen Zahlenwert, der aus der Versionsnummer ermittelt wird - sofern es Regeln für den Aufbau einer solchen Nummer gibt - ablegen und diesen für Sortierungen und min/max benutzten.
In Java könnte man mit sowas m.M. nach leichter umgehen.

 

[Gast2]

Kann man nur die Function selber schreiben (Wieder mal PL/SQL) und dann im SELECT aufrufen.
Als Funktion könntest du z.B. eine absolute Number aus der Versionsnummer berechnen.

Der Select sähe dann so aus das:

SELECT Name, Version, UniqueDeviceID 
  FROM (
     SELECT Name, Version, UniqueDeviceID, MAX(MYSCHEMA.getVersionAsNumber(Version))
       FROM packages, collection, device_collection, devices
     WHERE packages.ID = package_ID AND collection.ID = collection_ID AND device_ID = devices.ID
     GROUP BY packages.name
  )

Die Function müsstest du dann in deinem Schema als Store Procedure anlegen:

CREATE OR REPLACE FUNCTION getVersionAsNumber(
    version IN VARCHAR2
) RETURN NUMBER IS
  returnValue NUMBER;
BEGIN
  -- hier einen algorithmus der die Versionsnummer in reguläre zahl konvertiert
  -- z.b. aus 1.43.18-6401 -> 14318.6401
  --       und 1.43.01-5310 -> 14301.5310
  --       und 2.12.03-1207 -> 21203.1207
  returnValue := ... <- und hier kannst du mal überlegen, INSTR,SUBSTR und TO_NUMBER werden dir helfen ;)
  --
  RETURN returnValue;
END;

 

[Generic1]

vielen Dank für die Antworten!!

Eine Frage hätte ich noch und zwar hab ich jetzt eine Erweiterung gemacht:

und ich habe momentan folgende Cursor:

CURSOR queryCurser IS SELECT PACKAGES.Name, PACKAGES.Version, DEVICES.UniqueDeviceID
		FROM PACKAGES, Collection, DEVICE_Collection, DEVICES
		WHERE PACKAGES.ID = Collection.PACKAGE_ID 
	  	  AND Collection.ID = DEVICE_Collection.Collection_ID 
	  	  AND DEVICE_Collection.DEVICE_ID = DEVICES.ID;

resultSetQuery queryCurser%ROWTYPE;

das klappt alles wunderbar -> wenn in die Tabelle Device_Collection ein Eintrag gemacht wird, dann wird ein Trigger ausgelöst und mittels des Triggers wird dann die Stored Procedure angestoßen, die dann den Cursor beinhaltet.

Jetzt hab ich noch die Tabelle Lifecycle dazugemacht, mit welcher der Lifecycle eines Packages nachvollzogen werden kann. D.h. also, wenn ein Package erzeugt wurde, dann wird für dieses Package (über Package_ID) in der Tabelle Lifecycle in der Spalte Type "ERZEUGT" eingetragen.

Über eine JComboBox kann man dann das Package auf "Geprüft" kennzeichnen -> dadurch wird wieder ein Eintrag in die Tabelle Lifecycle gemacht, mit dem Type "GEPRUEFT".

Wenn für ein Package dann ein Eintrag "GEPRUEFT" in der Tabelle Lifecycle gamcht wurde, dann sollte der Trigger angestoßen werden, welcher dann die SP wieder anstößt.

Im Cursor sollte dann geschaut werden, ob ein Eintrag "GEPRUEFT" vorhanden ist, aber kein Eintrag "ABGELAUFEN" und wenn ja, dann sollte eben "PackageName.Version -> UniqueDeviceID" mit dem Cursor selektiert werden.

Weiß da jemand eine Lösung, wie man den Cursor am besten umändert, damit das klappt?


1. Das erste was mir klar ist, ich muss den Trigger auf die Tabelle Lifecycle legen, wenn sich in dieser Tabelle was tut, dann sollte der Trigger nach dem Insert aufgerufen werden. Im Trigger überprüf ich dann, ob ein Eintrag mit dem Type=GEPRUEFT gemacht wurde, wenn ja, dann die SP aufrufen, wenn nein, dann nichts machen.

2. ich muss in der Cursor- Abfrage in der Tabelle Lifecycle nachsehen, ob für ein Package ein Eintrag mit dem Type "GEPRUEFT" vorhanden ist aber noch kein Eintrag "ABGELAUFEN" -> wenn ja, dann die PackageName -> UniqueDeviceID- Kombination selektieren, ansonsten dieses Package -> UniqueDeviceID- Tupel ignorieren

Bei Nummer 2. hab ich noch meine Probleme, da ich nicht weiß, wie ich im Cursor berücksichtigen soll, ob ein GEPRUEFT- Eintrag fürs Package vorhanden ist oder nicht.

lg

 

[Michael...]

2. ich muss in der Cursor- Abfrage in der Tabelle Lifecycle nachsehen, ob für ein Package ein Eintrag mit dem Type "GEPRUEFT" vorhanden ist aber noch kein Eintrag "ABGELAUFEN" -> wenn ja, dann die PackageName -> UniqueDeviceID- Kombination selektieren, ansonsten dieses Package -> UniqueDeviceID- Tupel ignorieren

Versteh zwar nicht, was genau Du vorhast. Aber...
Du hast ja einen Update Trigger auf der Tabelle Lifecycle, d.h. der/die Datensa(e)tz(e) die in Lifecycle geändert wurden sind dem Trigger bekannt (Wird überhaupt mehr als ein Eintrag gleichzeitig geändert). Dann musst Du ja nur noch prüfen ob in dem geänderten Datensatz der Wert auf "GEPRUEFT" steht und kannst über dieses Tupel Dich auf die gesuchte UniqueDeviceID durchhangeln und damit machen, was auch immer Du damit vorhast.

 

[Generic1]

Ichs habs ein bisschen kompliziert erklärt, geb ich zu,

Wenn ein Eintrag in der Tabelle Lifecycle gemacht wird, welcher den Typ GEPRUEFT hat, dann wird eben der Trigger angestoßen und es sollten alle Packagenamen und dazugehörige UniqueDeviceIDs ausgegeben werden, für die in der Tabelle Lifecycle ein Eintrag GEPRUEFT aber kein Eintrag ABGELAUFEN vorhanden ist.

Mir ist nicht ganz klar, wie ich meinen Cursor für das erweitern muss:

CURSOR queryCurser IS SELECT PACKAGES.Name, PACKAGES.Version, DEVICES.UniqueDeviceID
		FROM PACKAGES, Collection, DEVICE_Collection, DEVICES
		WHERE PACKAGES.ID = Collection.PACKAGE_ID 
	  	  AND Collection.ID = DEVICE_Collection.Collection_ID 
	  	  AND DEVICE_Collection.DEVICE_ID = DEVICES.ID
                  [B]AND LIFECYCLE.TYPE="GEPRUEFT" gibt es 
                  AND LIFECYCLE.TYPE="ABGELAUFEN" gibts nicht;[/B]

 

[Michael...]

Du musst ja auch die Tabelle im FROM Teil des Statements angeben und in der WHERE Klausel über entsprechende Bedingungen mit den anderen Tabellen verknüpfen.
Ganz schön mutig, ohne SQL Kenntnisse Oracle Prozeduren zu schreiben ;-)

 

[Generic1]

Da würd ich so nicht sagen aber bitte,
mir gehts darum wie ich die Abfrage macht, ob ein "GEPRUEFT" vorhanden ist und ein "ABGELAUFEN" eben nicht, das ich die Tabelle LIFECYCLE in FROM angeben muss, versteht sich ja von selbst war ich der meinung.

 

[Michael...]

das ich die Tabelle LIFECYCLE in FROM angeben muss, versteht sich ja von selbst war ich der meinung.

Warum machst Du es dann nicht?

Was ich gestern ganz übersehen habe: Lifecycle.type kann ja nicht gleichzeitig zwei Werte annehmen, was soll dann diese Überprüfung in der Where Klausel machen. Die würde ja immer false zurückliefern.

                  [B]AND LIFECYCLE.TYPE="GEPRUEFT" gibt es 
                  AND LIFECYCLE.TYPE="ABGELAUFEN" gibts nicht;[/B]

 

[Generic1]

eben, das ist ja meine Frage, wie kann ich in einer SELECT- Abfrage abfragen, ob es in einer Tabelle einen Eintrag GEPRUEFT gibt aber keinen eintrag ABGELAUFEN:

ID TYPE StateComment When User_ID Package_ID

so schaut ja ein Tupel in der Tabelle LIFECYCLE aus und meine Frage wäre jetzt, wie ich in der Select abfrage für eine PACKAGE_ID abfragen kann, ob für diese PACKAGE_ID ein Eintrag TYPE=GEPRUEFT aber kein Eintrag TYPE=ABGELAUFEN existiert, also wenn in der Tabelle LIFECYCLE folgende Tupel sind:

1  GEPRUEFT -  -  -  1254   // 1254 ist die Package_ID
2  ABGELAUFEN - - - 1254

dann sollte das Package.NAME und UniqueDeviceID nicht selektiert werden. Gibt es hingegen in der Tabelle LIFECYCLE nur den Eintrag

1  GEPRUEFT -  -  -  1254   // 1254 ist die Package_ID

aber keinen Eintrag 2 ABGELAUFEN - - - 1254, dann sollte der Package.NAME un UniqueDeviceID selektiert werden.

Meine Frage wäre jetzt, wie mache ich diese Abfrage in der SELECT- Anweisung?
lg

 

[Michael...]

Jetzt hab ich das Problem verstanden. Dein Datenmodell scheint da etwas ungünstig gewählt zu sein.
Kann es überhaupt vorkommen, dass ein Eintrag zur PackageID auf geprüft gesetzt wird und zur selben ID ein Eintrag ABGELAUFEN bereits existiert?

Lösen kann man das mit einem Subselect a la:

Select ... Where Type = 'GEPRUEFT' and PackageID not in (Select PackageID From Lifecycle Where Type = 'ABGELAUFEN')

Würde mir aber überlegen, ob man das im Datenmodell nicht besser abbilden kann.

 

[Generic1]

Hallo,

vielen Dank mal für den Vorschlag, habs so in diese Richtung umgesetzt und funktioniert auch sehr gut:

... AND PACKAGES.ID IN  
	(SELECT Package_ID FROM LIFECYCLE, PACKAGES WHERE Type = 'GEPRUEFT' 
         AND Package_ID=PACKAGES.ID 
         AND Package_ID NOT IN (SELECT Package_ID FROM LIFECYCLE WHERE Type = 'AUSGELAUFEN' OR Type = 'ABGELEHNT'));

Jetzt hat sich aber noch was geändert, und zwar kann es sein, dass die Tabelle so aussieht:

1  GEPRUEFT -  -   DATUM1  1254   // 1254 ist die Package_ID
2  ABGELAUFEN - - DATUM2  1254
3  GEPRUEFT -  -   DATUM3  1254

Also es kann sein, dass ein Package schon abgelaufen war aber dann wieder Geprüft wird.
Ich müsste jetzt ein SELECT für eine Package_ID machen (in diesem Fall 1254) und dann schaun, ob der letzte Eintrag (Anhand des Datums - in diesem Fall DATUM3) für diese Package_ID GEPRUEFT.

Weiß jemand wie ich die SELECT da am besten mache?
Vielen Dank!!
lg

 

[Michael...]

Sicherlich kann man das durch ein entsprechendes Statement lösen (evtl. ein weiteres Sub Select)

Was allerdings unschön ist und auch nicht notwendig sein sollte:
- da Du ja einen Trigger auf der Tabelle hast: Einfach nur die neuen Einträge (die ja den Trigger auslösen), ob deren Feld Type den Wert 'GEPRUEFT' enthält und schon hast Du die entsrpechende(n) ID(s)
- Alternativ, kann man das Datenmodell korrigieren. Denn die Tabelle Lifecycle scheint ja dem Logging zu dienen. Geprüft, Abgelaufen... scheinen aber Eigenschaften des Objekts mit der ID zu sein und sollte eher ein Feld in der Tabelle Packages sein? Denn wenn ich das richtig deute geht es doch darum den aktuellen Status herauszufinden?