Oracle SQL Abfrage auf gleiche Tabelle

[tuningmaster]

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

[carstenj]

Hi,

wie genau sind denn deine Tabelle aufgebaut?

[tuningmaster]

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

[chE92]

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

[Jimbo0915]

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

[Jimbo0915]

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

[tuningmaster]

Hi,

das sieht dann so aus.

|U_ID|R_ID |

| 1 |1|

| 1 |2|

| 1 |3|

Viele Grüße

Michael

[Klotzkopp]

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]

[tuningmaster]

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 :/

[Klotzkopp]

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.

[Jimbo0915]

Klotzkopp hat recht

[tuningmaster]

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??

[tuningmaster]

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

;

[tuningmaster]

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);

[tuningmaster]

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 :/

[Klotzkopp]

Oracle mag offenbar AS nicht. Lass die beiden as einfach mal weg.

Bearbeitet 5. Mai 2014 von Klotzkopp

[Jimbo0915]

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

[tuningmaster]

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));

[Jimbo0915]

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)))

[tuningmaster]

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 ^^

[Klotzkopp]

Hast du meine Abfrage mal ohne "AS" ausprobiert?

[tuningmaster]

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))

[tuningmaster]

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