Veröffentlicht 5. Mai 201411 j Hallo Freunde, wie kann ich mit Oracle SQL aus einer Datenbanktabelle verschiedene Datensätze abfragen. Hintergrund: Wir haben ein neues Rollen Konzept in unserer Anwendung eingeführt. In der Tabelle xxx_Rolle gibt es verschiedene Rollen, die ein User besitzen kann (ggf. auch n-Rollen) 1) Planen 2) Kalkulieren 3) Nach SAP abgeben 4) Löschen User A besitzt Rolle 1 + 3 User B besitzt Rolle 1 User C besitzt Rolle 3 User D besitzt Rolle 1+4 Nun haben wir die Anforderung bekommen, explizit die User auszugeben, die Rolle 1+3 sowie 1+4 besitzen. Wenn ich in SQL mit "AND" arbeite, bekomme ich fälschlicherweise User A,B,C,D Richtig wäre eigentlich User A und User D. Daher meine Frage als SQL Neuling: Welches Skript oder Join bzw. Anweisung gibt mir das richtige Ergebnis aus? Vielen Dank für eure Hilfe und Ratschläge Freundliche Grüße Michael
5. Mai 201411 j Autor Tabelle "XXX_USER" <<--Zuordnungstabelle Userid zum Usernamen Spalte: UserID Spalte: USER_NAME Tabelle "XXX_USER_ROLLE" <<-- Hier hat ein User ggf. n-Rollen Spalte: UserID Spalte: Rolle_ID Tabelle "XXX_ROLLE" <<-- Definitionstabelle Rolle_ID zu Beschreibung (Was macht die Rolle) (Ist aber nur als Info gedacht, wird nicht im SQL Skript verwendet) Spalte: Rolle_ID Spalte: Rolle_Beschreibung_zur_ID
5. Mai 201411 j Hi, kruze Zwischenfrage: Wenn es einen User mit 3 Rollen gibt heißt das, dass es einen Eintrag in der Tabelle "XXX_USER_ROLLE" gibt der so aufgebaut ist ? Tabelle: "XXX_USER_ROLLE" |U_ID|R_ID | | 1 |1,2,3| che
5. Mai 201411 j Hi tuningmaster, folgendes wird funktionieren, ist allerdings fernab von Performant. Fürs Livesystem also nicht zu empfehlen. _____________________________________________________________________________________ SELECT UserID FROM XXX_USER WHERE EXISTS(SELECT * FROM XXX_USER_ROLLE WHERE XXX_USER_ROLLE.UserID = XXX_USER.UserID AND XXX_USER_ROLLE.Rolle_ID = 1) AND EXISTS(SELECT * FROM XXX_USER_ROLLE WHERE XXX_USER_ROLLE.UserID = XXX_USER.UserID AND XXX_USER_ROLLE.Rolle_ID IN(3,4)) _______________________________________________________________________________________ LG Jimbo
5. Mai 201411 j Hi, kruze Zwischenfrage: Wenn es einen User mit 3 Rollen gibt heißt das, dass es einen Eintrag in der Tabelle "XXX_USER_ROLLE" gibt der so aufgebaut ist ? Tabelle: "XXX_USER_ROLLE" |U_ID|R_ID | | 1 |1,2,3| che hört sich eher nach n : m an, oder nicht? LG Jimbo
5. Mai 201411 j Autor Hi, das sieht dann so aus. |U_ID|R_ID | | 1 |1| | 1 |2| | 1 |3| Viele Grüße Michael
5. Mai 201411 j SELECT t1.UserID FROM XXX_USER_ROLLE as t1 INNER JOIN XXX_USER_ROLLE as t2 ON t1.UserID = t2.UserID WHERE t1.Rolle_ID = 1 AND t2.Rolle_ID = 4 OR t1.Rolle_ID = 1 AND t2.Rolle_ID = 3 [/code]
5. Mai 201411 j Autor Hallo Klotzkopf, damit würde er mir auch alle anderen tausend User ausgeben, die nur Rolle "1" besitzen. Ziel ist es nur die User mit Rolle 1+3 bzw. 1+4 wiederzugeben :/
5. Mai 201411 j Hallo Klotzkopf,Fast. damit würde er mir auch alle anderen tausend User ausgeben, die nur Rolle "1" besitzen.Hast du das ausprobiert? Bei mir funktioniert das nämlich.
5. Mai 201411 j Autor Hallo Jimbo, das Skript habe ich auf die richtigen Tabellennamen modifiziert. Das läuft fehlerfrei durch. Irgendwie bekomme ich trotzdem zuviele User angezeigt. Da sind ganz viele User bei, die die Rolle 1 und 3 haben, die 4. jedoch nicht. Trotzdem erscheinen die in der Ausgabe. Hmm...kann SQL so etwas überhaupt??
5. Mai 201411 j Autor Ich bekomme bei deinem Skript folgenden Fehler: Fehler beim Start in Zeile 1 in Befehl: SELECT t1.UserID FROM DIM_USER_ROLLE as t1 INNER JOIN DIM_USER_ROLLE as t2 ON t1.UserID = t2.UserID WHERE t1.Rolle_ID = 1 AND t2.Rolle_ID = 4 OR t1.Rolle_ID = 1 AND t2.Rolle_ID = 3 Fehler bei Befehlszeile:1 Spalte:37 Fehlerbericht: SQL-Fehler: ORA-00933: SQL-Befehl wurde nicht korrekt beendet 00933. 00000 - "SQL command not properly ended" *Cause: *Action: SELECT t1.UserID FROM DIM_USER_ROLLE as t1 INNER JOIN DIM_USER_ROLLE as t2 ON t1.UserID = t2.UserID WHERE t1.Rolle_ID = 1 AND t2.Rolle_ID = 4 OR t1.Rolle_ID = 1 AND t2.Rolle_ID = 3 ;
5. Mai 201411 j Autor Habe das o.g. Skript nochmal ausgeführt. Trotz, dass ich dort ein "not in" eingefügt habe, gibt er mir alle User aus, die explizit die Rollen haben, die abgefragt werden sollen + weitere Rollen im Profil haben (bspw. Rolle ID 30). Das "not in" interessiert die SQL Abfrage überhaupt gar nicht..Mit und Ohne "not in" bekomme ich immer 150 User ausgegeben. SELECT User_Name FROM DIM_USER WHERE EXISTS(SELECT * FROM DIM_USER_ROLLE WHERE DIM_USER_ROLLE.UserID = DIM_USER.UserID AND DIM_USER_ROLLE.Rolle_ID = 1) AND EXISTS(SELECT * FROM DIM_USER_ROLLE WHERE DIM_USER_ROLLE.UserID = DIM_USER.UserID AND DIM_USER_ROLLE.Rolle_ID IN(2,5,6) and dim_user_rolle.rolle_ID not in 30);
5. Mai 201411 j Autor Also man muss sich vorstellen, wir haben 30 Rollen zur Auswahl. Die User haben manchmal 4, manchmal 6 oder manchmal auch nur 1 Rolle im Profil vorhanden. Bei der o.g. SQL Abfrage erhalte ich User mit den Rollen (1,2,5,6 und weitere Rollen wie z.B. 30). Das verfälscht das gewünschte Ergebnis :/
5. Mai 201411 j Oracle mag offenbar AS nicht. Lass die beiden as einfach mal weg. Bearbeitet 5. Mai 201411 j von Klotzkopp
5. Mai 201411 j Hallo Jimbo, das Skript habe ich auf die richtigen Tabellennamen modifiziert. Das läuft fehlerfrei durch. Irgendwie bekomme ich trotzdem zuviele User angezeigt. Da sind ganz viele User bei, die die Rolle 1 und 3 haben, die 4. jedoch nicht. Trotzdem erscheinen die in der Ausgabe. Hmm...kann SQL so etwas überhaupt?? Wie gesagt, das funktioniert bei mir, ist aber natürlich dirty as possible. Deine Anforderung ist doch: Wenn der USER entweder die Rollen 1 und 3 hat oder die Rollen 1 und 4, dann .... oder nicht? Dann sollten sowohl Klotzkopps Lösung (schön) und meine (unschön) das richtige Ergebnis ausspucken. LG
5. Mai 201411 j Autor Genau, ich habe folgende Anforderung: Profil1 soll können: Planen Profil2 soll können: Kalkulieren Profil3 soll können: Nach SAP abgeben Profil4 soll können: Löschen User A besitzt Rolle 1 + 3 User B besitzt Rolle 1 User C besitzt Rolle 3 User D besitzt Rolle 1+4 User E besitzt Rolle 5 User F besitzt Rolle 6 User F besitzt Rolle 1+7 Ziel: Gib alle Benutzer aus, die nur Rolle 1+3 oder bspw. 1+4 haben. Mit diesem Skript gibt der Developer mir alle User aus, die Rolle 1 + n Rollen haben, Rolle 3 +n Rollen haben, Rolle 1+3 haben (was ja auch richtig wäre). SELECT User_Name FROM DIM_USER WHERE EXISTS(SELECT * FROM DIM_USER_ROLLE WHERE DIM_USER_ROLLE.UserID = DIM_USER.UserID AND DIM_USER_ROLLE.Rolle_ID = 1) AND EXISTS(SELECT * FROM DIM_USER_ROLLE WHERE DIM_USER_ROLLE.UserID = DIM_USER.UserID AND DIM_USER_ROLLE.Rolle_ID IN(3));
5. Mai 201411 j Das ist sehr merkwürdig, weil es ja ganz offensichtlich: SELECT .... FROM .... WHERE BED1 = erfüllt AND! Bed2 = erfüllt. Wahrscheinlich versteht die Oracle DB den ****** von mir einfach nicht, (zurecht!). Halte dich lieber an Klotzkopps Lösung. Wenn du es hiermit trotzdem nochmal versuchen willst, versuche mal klammern WHERE ((EXISTS(Abfrage1)) AND (EXISTS(Abfrage2)))
5. Mai 201411 j Autor Hey, wenn ich die exists kopiere / einfüge und mit anderen Rollen ID´s belege, erhalte ich leider immer noch zuviele User in der Wiedergabe. Bspw. Exists .....ID 1 .....Exists ID 2 .....Exist ID 3..... Ziel: 1+3 Wiedergabe: User mit ID (1+3) und User mit (1+3 zusätzlich 2). Plopp....mir ist gerade der Geduldsfaden gerissen mit dieser Datenbank ^^
5. Mai 201411 j Autor Ohne AS und mit Tabellenname.Tabellenspalte bekomme ich zwar keine Fehlermeldung, aber auch kein Ergebnis ausgespuckt...Irgendwo ist hier noch der Wurm drin. Die Ausgabe von Jimbo funktioniert schon fast: Bspw. Profil 1: Rolle 1,2,3 Profil 2: Rolle 1,2,3,4,5,6 Wenn ich Profil 1 abfrage, bekomme ich das richtige Ergebnis...Das klappt jetzt wunderbar.... Nur wenn ich Profil 2 abfrage, erhalte ich alle User aus Profil 1 und Profil 2 ....Die User aus Profil 1 sollen ja nicht in die Wiedergabe... (das funktioniert) SELECT User_Name FROM DIM_USER WHERE EXISTS(SELECT * FROM DIM_USER_ROLLE WHERE DIM_USER_ROLLE.UserID = DIM_USER.UserID AND DIM_USER_ROLLE.Rolle_ID = 1) and EXISTS(SELECT * FROM DIM_USER_ROLLE WHERE DIM_USER_ROLLE.UserID = DIM_USER.UserID AND DIM_USER_ROLLE.Rolle_ID IN(2)) and EXISTS(SELECT * FROM DIM_USER_ROLLE WHERE DIM_USER_ROLLE.UserID = DIM_USER.UserID AND DIM_USER_ROLLE.Rolle_ID IN(3)) (hier gibt er mir zu Profil2 noch alle User aus Profil1 mit aus, so sollte das natürlich nicht sein) SELECT User_Name FROM DIM_USER WHERE EXISTS(SELECT * FROM DIM_USER_ROLLE WHERE DIM_USER_ROLLE.UserID = DIM_USER.UserID AND DIM_USER_ROLLE.Rolle_ID = 1) and EXISTS(SELECT * FROM DIM_USER_ROLLE WHERE DIM_USER_ROLLE.UserID = DIM_USER.UserID AND DIM_USER_ROLLE.Rolle_ID IN(2)) and EXISTS(SELECT * FROM DIM_USER_ROLLE WHERE DIM_USER_ROLLE.UserID = DIM_USER.UserID AND DIM_USER_ROLLE.Rolle_ID IN(3)) and EXISTS(SELECT * FROM DIM_USER_ROLLE WHERE DIM_USER_ROLLE.UserID = DIM_USER.UserID AND DIM_USER_ROLLE.Rolle_ID IN(4)) and EXISTS(SELECT * FROM DIM_USER_ROLLE WHERE DIM_USER_ROLLE.UserID = DIM_USER.UserID AND DIM_USER_ROLLE.Rolle_ID IN(5)) and EXISTS(SELECT * FROM DIM_USER_ROLLE WHERE DIM_USER_ROLLE.UserID = DIM_USER.UserID AND DIM_USER_ROLLE.Rolle_ID IN(6))
5. Mai 201411 j Autor Habs....mit "not exists" komme ich auf mein Ergebnis....Muss das hier nochmal bei den anderen Profilen prüfen. Vielen Dank für eure Ratschläge und Tipps.... Viele Grüße Michael
Erstelle ein Konto oder melde dich an, um einen Kommentar zu schreiben.