Komplexer JOIN

[Basti]

Zurzeit arbeite ich an meinem bisher umfangreichsten System, das eine Art Informationssystem werden soll.

Innerhalb dieses System gibt es verschiedene Kategorien, für die die Benutzer Rechte zugewiesen bekommen müssen, um zum Beispiel Einträge zu erstellen.
Das Rechtesystem ist so aufgebaut, dass es eine Benutzer-Tabelle (Users) und eine Gruppen-Tabelle (Groups) gibt.
Nun können entweder den Benutzern direkt die Rechte für eine Kategorie zugewiesen werden, und zwar in der Tabelle "UsersCats" (Spalten: user_id, cat_id) oder über die Gruppen.
Die Gruppen der Benutzer werden in der Tabelle "UsersGroups" (Spalten: user_id, group_id) gespeichert und die Kategorien der Gruppen ähnlich wie bei den Benutzern in der Tabelle "GroupsCats" (Spalten: group_id, cat_id).

Das Abrufen der Rechte etc. stellt auch kein Problem dar, da ich dort prüfe, ob der Benutzer entweder direkten Zugang zu einer Kategorie besitzt oder eine seiner Gruppen Zugang besitzt.

Nun habe ich aber vor, bei einigen Kategorien die Benutzer dieser Kategorien in einer Übersicht anzuzeigen, also alle die, die Zugang zu der jeweiligen Kategorie besitzen.
Dabei sollen dann alle Benutzer aufgelistet werden, die entweder direkten Zugang besitzen oder in einer Gruppe mit Zugang Mitglied sind, ohne dass ein Benutzer doppelt vorkommt.
Derzeit habe ich eine etwas unpraktische Lösung, die zudem nicht richtig funktioniert.

Da ich mich noch nicht allzu lange mit JOINS beschäftige und in meinem Fall der JOIN Befehl sehr komplex ist (wenn überhaupt in einem möglich) wollte ich mal fragen, ob vielleicht jemand von euch weiter weiß.
Ich habe zwar selbst schon ein paar Versuche angestellt, aber funktioniert hat es nicht

Ich würde mich über ein paar Hilfestellungen freuen, vielleicht habt ihr ja auch noch andere Ideen

[Basti]

Sorry für den Doppelpost, aber hat denn niemand irgendwelche Ideen oder wurde der Thread einfach übersehen?

[Manuel]

Sorry, gerade etwas im Lernstress wegen Softwaretechnik...

Hast du dazu vielleicht ein ER-Diagramm? Oder irgendwie eine Übersicht? Finde es gerade schwierig so aus den gebröckelten Informationen eine gescheite SQL-Abfrage zu formulieren.

Für wiederholende Datensätze gibt es übrigens in SQL den Befehl "DISTINCT". Also sowas wie SELECT DISTINCT....

Lg,
Manuel

Edit: Ach ja, welche SQL-Version steht dir zur Verfügung?

[Basti]

Danke schonmal für deine Antwort, das mit der Übersicht hätte ich direkt machen sollen, naja, ich habe jetzt mal versucht, etwas möglichst verständliches zu basteln.

Dort sind alle wichtigen Informationen eingetragen. Ich habe MySQL 4.1.19 auf meinem Server laufen.

Jetzt nochmal ne kurze Erläuterung, was ich vor habe, an der Übersicht sollte das ja leichter verständlich sein.
Also, es sollen alle Benutzer die Rechte für eine Kategorie haben, ausgegeben werden.
Dazu müssen alle Benutzer mit Direktzugängen zu den Kategorien ausgewählt werden und die, die Zugang über eine Gruppe erhalten, wobei der Weg über die Gruppen etwas umständlicher ist, und das ist derzeit mein Problem

Am Ende sollen dann alle Benutzer zurückgegeben werden, die entweder in der "UsersCats" Tabelle Zugang zu der jeweiligen Kategorie haben oder über eine ihrer Gruppen und die Tabelle "GroupsCats" Zugang erhalten.



Wär echt toll, wenn das in einem Befehl ginge, du musst mir auch nicht direkt den fertigen Befehl liefern, ein Ansatz würde es zur Not auch tun.

[Manuel]

Lustiges Diagramm
Also mit einer neueren MySQL Version wäre das glaube ich ein gutes Stück einfacher, denn MySQL 4 unterstützt leider noch keine Unteranfragen, die SQL meines Erachtens so unglaublich mächtig machen.
Ich mach mir darüber Morgen Abend mal Gedanken...heute schaff ich das leider nicht wegen der Klausur Morgen!

Bekommen das schon irgendwie hin, keine Sorge!

Lg,
Manuel

[Basti]

Ok, vielen Dank für deine Hilfe, kannst dir aber ruhig Zeit lassen, das eilt nicht.

Viel Erfolg für deine Klausur!

[Manuel]

Ich müsste jetzt selbst mal ausprobieren, denn mit PostgreSQL hab ich eine solche Anfrage schon formuliert und das geht auch mit einem Ausdruck dann.

Probieren wir einfach mal was rum:

Code: Alles auswählen

SELECT DISTINCT u.user_id FROM users u LEFT JOIN userscats c ON (c.user_id = u.user_id)
UNION ALL SELECT u.user_id FROM users u LEFT JOIN groups g ON (g.user_id = u.user_id) LEFT JOIN groupcats gc ON (gc.group_id = g.group_id)
ORDER BY u.user_id

Dazu muss dann natürlich noch die Kategorie "cats" gejoined werden und dementsprechend aussortiert werden. Das Problem ist, dass SELECT DISTINCT nun nur auf der ersten Anfrage Tupel rauswirft. Mit einer Unteranfrage könntest du einfach mehrere Anfragen mit UNION untereinander tun und dann ein gesamtes SELECT DISTINCT machen...aber schau einfach mal ob er das nicht vielleicht schon so macht oder schreib DISTINCT mal bei dem UNION ALL SELECT ebenfalls dabei.

Lg,
Manuel

[Basti]

Danke, das hilft mir schonmal sehr viel weiter.
Ich denke insbesondere das DISTINCT und UNION ALL sind Sachen, die ich brauchte.

Zwar funktioniert es im Moment noch nicht, aber ich muss mal weiter ausprobieren, vielleicht liegt es auch an meiner MySQL Version.

Dann habe ich noch eine Frage zu den "Abkürzungen" der Tabellennamen, weil ich das bisher noch nicht kannte. Funktioniert das vom Prinzip her so, wie das "AS" bei Spaltennamen, also dass man diese einfach umbennent bzw. abkürzt, um sie leicht weiterzuverwenden.
Das macht die Queries nämlich wesentlich übersichtlicher, aber bisher kannte ich das wie gesagt noch nicht

[Manuel]

Das AS kannst du quasi weglassen in diesem Fall. Ich verwende das vor allem bei JOINS, da es sonst viel zu unübersichtlich wird.

[Basti]

So, ich habe jetzt nochmal weiter gemacht, aber leider bleibe ich an einem ungeklärten Fehler hängen.

Hier mein aktueller Code:

Code: Alles auswählen

SELECT DISTINCT u.id FROM Users u LEFT JOIN UsersCats uc ON (uc.user_id=u.id AND uc.cat_id='".CAT_ID."')
UNION ALL SELECT DISTINCT u.id FROM Users u LEFT JOIN UsersGroups ug ON (ug.user_id=u.id) LEFT JOIN GroupsCats gc ON (gc.group_id=ug.group_id AND gc.cat_id='".CAT_ID."')
ORDER BY u.status DESC,u.lastname ASC

Komischerweise beschwert sich MySQL nun, dass die Tabelle (u bzw. Users) in der ORDER-Anweisung unbekannt ist (Unknown table 'u' in order clause).
An der Abkürzung liegt es nicht, da ich auch schon die volle Ausschreibung des Tabellennamens probiert habe.

Ich habe zwar auch schon etwas im Internet gesucht, konnte bisher aber noch keine Lösung finden.

[Manuel]

Wirds denn korrekt ausgeführt wenn du das ORDER BY weglässt?

[Basti]

Ja, hab's grad mal ausprobiert, allerdings erhalte ich für unterschiedliche Kategorien immer die gleichen Ergebnisse und es gibt doppelte Benutzer.

Irgendwie komisch, kann es vielleicht damit zusammenhängen, dass man zwei SELECT-Abfragen verbindet und diese dann ordnet, kenn mich damit nicht so genau aus.
Über den Rest muss ich jedenfalls auch nochmal sehen, da anscheinend noch etwas nicht stimmt

[Basti]

So, ich habe mich jetzt wiedermal dran gesetzt und alle Probleme bis auf das mit dem Sortieren gelöst.

Zunächst ein mal hatte ich für jede Kategorie die gleichen Ergebnisse, da du in deinem Query ja einen LEFT JOIN benutzt hast, da in meinen Abfragen aber alle ON-Bedingungen zutreffen müssen, habe ich nun einen INNER JOIN verwendet, daran hatte ich zuerst aber auch nicht gedacht.

Das Problem mit den doppelten Benutzern wenn diese direkte Zugänge und Gruppenzugänge hatten, habe ich dadurch gelöst, dass ich anstatt von "UNION ALL" nun "UNION DISTINCT" verwendet habe, was eigentlich auch Sinn ergibt

Hier mal mein aktueller SQL-Befehl, für diejenigen die es interessieren sollte:

Code: Alles auswählen

SELECT DISTINCT u.id FROM Users u INNER JOIN UsersCats uc ON (uc.user_id=u.id AND uc.cat_id='".CAT_ID."')
UNION DISTINCT SELECT DISTINCT u.id FROM Users u INNER JOIN UsersGroups ug ON (ug.user_id=u.id) INNER JOIN GroupsCats gc ON (gc.group_id=ug.group_id AND gc.cat_id='".CAT_ID"')
ORDER BY u.status DESC,u.lastname ASC

Um das "ORDER BY" Problem zu lösen werde ich jetzt nochmal ein wenig durch's Internet forsten ^^

Vielen Dank schonmal an dich, Manuel, ohne deine Hilfe hätte ich das wohl nicht hinbekommen

// edit: Mithilfe des SELFHTML-Forums konnte ich das Problem nun doch noch lösen, sodass nun auch alles funktioniert.
Es lag daran, dass man zum Sortieren beim Verbinden von mehreren SELECT-Befehlen jeweils Klammern darum setzen muss und den zu sortierenden Spalten identische Aliases geben muss.

Das ganze sieht dann so aus:

Code: Alles auswählen

(SELECT DISTINCT u.id, u.status AS status, u.lastname AS lastname FROM Users u INNER JOIN UsersCats uc ON (uc.user_id=u.id AND uc.cat_id='".CAT_ID."'))
UNION DISTINCT
(SELECT DISTINCT u.id, u.status AS status, u.lastname AS lastname FROM Users u INNER JOIN UsersGroups ug ON (ug.user_id=u.id) INNER JOIN GroupsCats gc ON (gc.group_id=ug.group_id AND gc.cat_id='".CAT_ID"'))
ORDER BY status DESC,lastname ASC