JOIN innerhalb einer Tabelle

logan517 · 19 August 2016

Hallo,

ich habe folgende Tabellen:

In der Sites Tabelle sind alle verfügbaren Seiten festgehalten und dieser über die SitesFunctions bestimmte API-Funktionen in der Functions zugeordnet.

Der Plan ist es, allen Verfügbaren Seiten gewisse Funktionen zuzuordnen.
Dann verknüpfe ich eine UserID mit einer Seite und diese hat dann Zugriff auf die Seite und die dafür benötigten API-Funktionen.

Mein Problem ist es nun, eine SQL Abfrage so zu bauen, dass ich nur eine UserID angeben muss, und alle Funktionen zurück bekomme, auf die der Nutzer über irgendeine Seite Zugriff hat.

Beispielhaft hier mal die DB Sites

Ich habe bis jetzt folgenden Join zusammen gebaut, welche nur zum Teil funktioniert:

Code:

SELECT *
FROM Users as U

LEFT JOIN Sites AS S
ON S.pkSiteID = U.fkSiteID
OR S.ParentID = U.fkSiteID

LEFT JOIN SitesFunctions as SF
ON SF.fkSiteID = S.pkSiteID

LEFT JOIN Functions as F
ON F.pkFuncID = SF.fkFuncID

WHERE pkBenID = '215'

Leider ist da noch das Problem, wenn ich einem Nutzer nun die Berechtigung auf die Seite mit der ID 0 oder z.B. 3 gebe, dann bekommt dieser nicht die darunter liegenden Seite zugeordnettr
(Im Screenshot hier wird das etwas genauer was ich meine)

Mit dem bisschen SQL von oben sind meine Kenntnisse da auch leider schon am Ende.
Ich würde nun Anfragen, ob z.B. das Feld Sites.link NULL ist und dann davon die pkSiteID nehmen und alle Seite mit der ParentID = pkSiteID hinzufügen, aber wie ich das in SQL umsetze, pffffffff

Danke
Grüße
Logan517

akretschmer · 19 August 2016

logan517 schrieb:
Ich würde nun Anfragen, ob z.B. das Feld Sites.link NULL ist und dann davon die pkSiteID nehmen und alle Seite mit der ParentID = pkSiteID hinzufügen, aber wie ich das in SQL umsetze, pffffffff

Mit einer rekursiven Abfrage. Google mal, da findest bestimmt einige passende Beispiele.

logan517 · 19 August 2016

akretschmer schrieb:
Mit einer rekursiven Abfrage.

Ich bin auf die Seite gestoßen: Rekursives SQL - mächtig, aber gefährlich und habe mit dem dortigen Beispiel folgende Abfrage hinbekommen:

Code:

WITH ABCDE (pkSiteID, ParentID, Name, Icon, link) AS
(
SELECT pkSiteID, ParentID, Name, Icon, link
    FROM Sites
    WHERE pkSiteID != 0 AND ParentID = 1

UNION ALL

SELECT A.pkSiteID, A.ParentID, A.Name, A.Icon, A.link
    FROM Sites AS A
        INNER JOIN ABCDE AS B
            ON A.ParentID = B.pkSiteID
)

SELECT *
FROM ABCDE

So bekomme ich meine Tabelle schon mal richtig zurück.
Also habe ich versucht meine JOINS von oben einzubauen, allerdings ohne Erfolg.
Sobald mehr als eine Verknüpfung in der SitesFunctions Tabelle gefunden wird, bekomme ich nichts mehr angezeigt:

Code:

WITH ABCDE (pkSiteID, ParentID, Name, Icon, link, pkRightsID, pkBenID, fkRoleID, fkSiteID, fkFuncID, pkFuncID, ClASsname, Functionname) AS
(
SELECT S.pkSiteID, S.ParentID, S.Name, S.Icon, S.link, UR.pkBenID, UR.fkSiteID, SF.fkFuncID, F.pkFuncID, F.ClASsname, F.Functionname
    FROM Users AS UR
        INNER JOIN Sites AS S
            ON S.pkSiteID = UR.fkSiteID
            OR S.ParentID = UR.fkSiteID
        INNER JOIN SitesFunctions AS SF
            ON SF.fkSiteID = S.pkSiteID
        INNER JOIN Functions AS F
            ON F.pkFuncID = SF.fkFuncID
    WHERE S.pkSiteID != 0

UNION ALL

SELECT S.pkSiteID, S.ParentID, S.Name, S.Icon, S.link, UR.pkBenID, UR.fkSiteID, SF.fkFuncID, F.pkFuncID, F.ClASsname, F.Functionname
    FROM User AS UR
        INNER JOIN Sites AS S
            ON S.pkSiteID = UR.fkSiteID
            OR S.ParentID = UR.fkSiteID
        INNER JOIN SitesFunctions AS SF
            ON SF.fkSiteID = S.pkSiteID
        INNER JOIN Functions AS F
            ON F.pkFuncID = SF.fkFuncID

        INNER JOIN ABCDE
        ON S.ParentID = ABCDE.pkSiteID
    WHERE S.pkSiteID != 0
)

SELECT *
FROM ABCDE
WHERE pkBenID = '215'

Jemand ne Idee warum?

ukulele · 22 August 2016

Kannst du mal Beispieldatensätze aus all deinen Tabellen für einen rekursive Site-Suche posten?

logan517 · 24 August 2016

Hi,

hab deine Nachricht, ukulele, leider heute Mittag erst gesehen.

Ich habe mal ein SQl Dump angehangen.
(Sind momentan nur Testdatensätze)

Grüße

ukulele · 25 August 2016

Also wenn ich das richtig verstehe gibt dein Code WITH ABCDE ohne die ganzen Joins schon die richtigen Sites aus, das sind Datensätze mit pkSiteID 2 bis 6. Deine Users Tabelle hat aber nur fkSiteID 1 und 12 als Datensatz in den Testdaten, da kann man schonmal nicht sinnhaft joinen.

Ich denke wenn du die Sites über WITH ABCDE richtig joinst, kannst du beim Select darauf die anderen Tabellen dazu joinen. So musst du nicht alle Joins im WITH Teil mehrfach machen. Ich hatte jetzt allerdings beim Testen gleich das Problem oben, ich kann mal posten wie ich mir das vorstelle:

Code:

WITH ABCDE (pkSiteID, ParentID, Name, Icon, link) AS
(
SELECT pkSiteID, ParentID, Name, Icon, link
  FROM Sites
  WHERE pkSiteID != 0 AND ParentID = 1

UNION ALL

SELECT A.pkSiteID, A.ParentID, A.Name, A.Icon, A.link
  FROM Sites AS A
  INNER JOIN ABCDE AS B
  ON A.ParentID = B.pkSiteID
)
SELECT   s.pkSiteID,
     s.ParentID,
     s.Name,
     s.Icon,
     s.link,
     UR.pkBenID,
     UR.fkSiteID,
     SF.fkFuncID,
     F.pkFuncID,
     F.ClASsname,
     F.Functionname
FROM   Users u
LEFT JOIN ABCDE s
ON     u.fkSiteID = s.pkSiteID
LEFT JOIN SitesFunctions SF
ON     SF.fkSiteID = S.pkSiteID
LEFT JOIN Functions F
ON     F.pkFuncID = SF.fkFuncID

logan517 · 25 August 2016

ukulele schrieb:
Also wenn ich das richtig verstehe gibt dein Code WITH ABCDE ohne die ganzen Joins schon die richtigen Sites aus

jup, ich bekomme dort diese Ausgabe, wobei hier auch manche Datensätze doppelt sind (wieso auch immer)

Code:

pkSiteID ParentID Name icon link
1    0    Seite_1    font    NULL
2    1    Seite_1_1    key    link_1_1
2    1    Seite_1_1    key    link_1_1
3    1    Seite_1_2    book    NULL
3    1    Seite_1_2    book    NULL
4    3    Seite_1_2_1    pencil    link_1_2_1
4    3    Seite_1_2_1    pencil    link_1_2_1
4    3    Seite_1_2_1    pencil    link_1_2_1
5    3    Seite_1_2_2    refresh    link_1_2_2
5    3    Seite_1_2_2    refresh    link_1_2_2
5    3    Seite_1_2_2    refresh    link_1_2_2
6    1    Seite_1_3    refresh    link_1_3
6    1    Seite_1_3    refresh    link_1_3
12    0    Seite_2    download    NULL
13    12    Seite_2_1    file-text    link_2_2
13    12    Seite_2_1    file-text    link_2_2
14    12    Seite_2_2    font    link_2_2
14    12    Seite_2_2    font    link_2_2

ukulele schrieb:
das sind Datensätze mit pkSiteID 2 bis 6. Deine Users Tabelle hat aber nur fkSiteID 1 und 12 als Datensatz in den Testdaten, da kann man schonmal nicht sinnhaft joinen.

Gerade das will ich ja mit der Rekursion hinbekommen.
Also wenn ein Nutzer Zugriff auf die Seite mit der 1 (Seite_1) hat, dann soll er auch auf alle darunter liegenden (2, 3, 4, 5, 6, ...)

ukulele · 26 August 2016

Code:

WITH t AS (
SELECT   u.pkBenID,
     s1.pkSiteID,
     s1.ParentID,
     s1.Name,
     s1.Icon,
     s1.link
FROM   users u
INNER JOIN sites s1
ON     u.fkSiteID = s1.pkSiteID
--WHERE   u.pkBenID = '215'
UNION ALL
SELECT   t.pkBenID,
     s2.pkSiteID,
     s2.ParentID,
     s2.Name,
     s2.Icon,
     s2.link
FROM   sites s2
INNER JOIN t
ON     s2.ParentID = t.pkSiteID
WHERE   s2.pkSiteID != t.pkSiteID
     )
SELECT   t.*,
     f.*
FROM   t
LEFT JOIN SitesFunctions sf
ON     t.pkSiteID = sf.fkSiteID
LEFT JOIN Functions f
ON     sf.fkFuncID = f.pkFuncID
ORDER BY t.pkBenID,t.pkSiteID

Die auskommentierte WHERE-Bedingung würde es nur für einen User erstellen, die pkBenID 215 aus deinem Beispiel gibts aber nicht in meinen Test-Daten. Wenn die ganze Tabelle nur für einen User erstellt wird, macht natürlich auch die Ausgabe von pkBenID keinen Sinn. Man kann das aber auch als View für alle User anlegen und in der View dann anhand der pkBenID suchen, ginge auch.

Der Rest müsste das liefern was du suchst. Ich würde vielleicht dem Root-Node NULL als ParentID mit geben, so musst du keine Rekursion mit WHERE s2.pkSiteID != t.pkSiteID oder anderweitig abfangen.

logan517 · 26 August 2016

Hi,
das funktioniert perfekt, dafür schon mal vielen Dank.

Mittlerweile kam noch eine Funktion hinzu und ich weiß nicht ob sich diese auch in SQL umsetzen lässt:
-> Userrollen
Ich habe nun die DB einfach mal um 2 Tabellen erweitert

Meine Idee dahinter war es:
Einem Nutzer entweder direkt zu einer Seite zuzuordnen, oder diesem einer Rolle zuweisen und der Rolle dann Sites.
(Im Anhang ist übrigens ein SQL Dump inkl. der 2 neuen Tabellen)

Mein erster Versuch war mal so, nur fehlt hier leider wieder die Rekursion für eine Rolle, also: User in Rolle 12 -> Zugriff auf die Seiten: 13 und 14

Code:

WITH t AS (
SELECT   u.pkBenID,
     s1.pkSiteID,
     s1.ParentID,
     s1.Name,
     s1.Icon,
     s1.link,
     rs.fkSiteID as rsSiteID
FROM   users u

LEFT JOIN Role_Sites AS rs
ON     u.fkRoleID = rs.fkRoldeID

LEFT JOIN sites s1
ON     u.fkSiteID = s1.pkSiteID
WHERE   u.pkBenID = 'ABSY1XFY43NKRJI46'
UNION ALL
SELECT   t.pkBenID,
     s2.pkSiteID,
     s2.ParentID,
     s2.Name,
     s2.Icon,
     s2.link,
     t.rsSiteID
FROM   sites s2
INNER JOIN t
ON     s2.ParentID = t.pkSiteID
OR     s2.pkSiteID = t.rsSiteID
WHERE   s2.pkSiteID != t.pkSiteID
     )
SELECT   t.*,
     f.*
FROM   t
LEFT JOIN SitesFunctions sf
ON     t.pkSiteID = sf.fkSiteID
OR     t.rsSiteID = sf.fkSiteID
LEFT JOIN Functions f
ON     sf.fkFuncID = f.pkFuncID
ORDER BY t.pkBenID,t.pkSiteID

(Diff Checker)

Oder sollte ich das nur komplett auf Rollen ummünzen und die direkt Verbindung zwischen User und Site weglassen?

Entschuldige bitte, dass ich das nochmal leicht umwerfe

Grüße

ukulele · 29 August 2016

Das finde ich ist jetzt aber irgendwie inkonsequent. Ein User kann nur einer Site zugeordnet werden oder müsste mehrfach in Users stehen. Es handelt sich also eigentlich um eine 1:1-Beziehung. Rollen haben eine n:m-Beziehung zu Sites, was für sich genommen logisch ist.

Du kannst natürlich Users oder Rollen Sites zuordnen, das ist durchaus in vielen Programmen möglich. Aber dann sollten beide n:m Beziehungen haben, sonst fehlt mir da irgendwie die Logik.

logan517 · 29 August 2016

Mein Plan war es die User mehrfach in der Users stehen zu haben. Es gibt noch auf einer anderen Datenbank eine Users-Tabelle, in der z.B: die pkBenID, Name, HashPasswort etc. steht. Mit dieser wird sich im Programm eingeloggt und dann erst im zweiten Schritt die function / berechtigung generiert.
Ich wollte dann einfach eine Abfrage im Sinne von [SELECT Class, Function FROM Table WHERE pkBenID = 'xyz'] machen und dann die zurückgegebene Tabelle ggf. in nem Array weiter verarbeiten.
Ich hatte mir das so gedacht, z.B.:
Ein User steht 3x in der Users
pkBenID| fkSiteID | fkRoleID
ABSY1XFY43NKRJI46 | null | 2
ABSY1XFY43NKRJI46 | null | 5
ABSY1XFY43NKRJI46 | 4 | null
(sind nun nur ausgedachte Werte, habe nun nicht geschaut ob es diese in der Test DB gibt)
Im Endeffekt sollte dann der Nutzer ABSY1XFY43NKRJI46 zugriff auf die Seite 2, 5 und alle Seiten, welche der Rollte 4 angeheftet sind.

ukulele · 29 August 2016

Dann ist Users defakto eine Zwischentabelle zwischen der Userstabelle der anderen DB und Sites oder Rollen. Sie bildet zwei verschiedene n:m-Beziehungen ab, einmal Users zu Sites und Users zu Rollen. Meiner Meinung nach gehört das in zwei Tabellen getrennt, oder gibt es zwischen dem SiteFK und dem RollenFK irgendeinen funktionalen Zusammenhang?

Abgesehen davon ist der Name Users irreführend, eigentlich sollte es Users_Sites und Users_Roles geben.

Ich baue dir das später anhand des DB Dumps mit ein.

logan517 · 29 August 2016

ukulele schrieb:
Dann ist Users defakto eine Zwischentabelle zwischen der Userstabelle der anderen DB und Sites oder Rollen. Sie bildet zwei verschiedene n:m-Beziehungen ab, einmal Users zu Sites und Users zu Rollen. Meiner Meinung nach gehört das in zwei Tabellen [...] eigentlich sollte es Users_Sites und Users_Roles geben.

Da spricht eigentlich nicht dagegen, ich kann das auch gerne 2 Tabellen machen. Kann dir nun keine Grüne nennen, warum ich das nur in eine gepackt habe.

ukulele schrieb:
[...]gibt es zwischen dem SiteFK und dem RollenFK irgendeinen funktionalen Zusammenhang?

Nein, das war einfach nur "historisch gewachsen" und bequemlichkeit, weil ich zuerst nur eine Verknüpfung zwischen Users und SiteID hatte und dann noch Rollen hinzu kamen, habe ich der Tabelle einfach eine Spalte hinzugefügt, statt eine neue Tabelle zu erstellen.

ukulele schrieb:
Ich baue dir das später anhand des DB Dumps mit ein.

Danke

ukulele · 29 August 2016

Ist eigentlich ganz einfach:

Code:

WITH Users_Sites AS (
   SELECT   pkBenID,
       fkSiteID
   FROM   Users
   WHERE   fkSiteID IS NOT NULL
   ), Users_Roles AS (
   SELECT   pkBenID,
       fkRoleID
   FROM   Users
   WHERE   fkRoleID IS NOT NULL
   ), t AS (
   SELECT   us2.pkBenID,
       s1.pkSiteID,
       s1.ParentID,
       s1.Name,
       s1.Icon,
       s1.link
   FROM   (   SELECT   DISTINCT us1.*
         FROM   (   SELECT   ur.pkBenID,
                   rs.fkSiteID
               FROM   Users_Roles ur
               INNER JOIN Role_Sites rs
               ON     ur.fkRoleID = rs.fkRoldeID
               UNION ALL
               SELECT   pkBenID,
                   fkSiteID
               FROM   Users_Sites
             ) us1
       ) us2
   INNER JOIN sites s1
   ON     us2.fkSiteID = s1.pkSiteID
   --WHERE   u.pkBenID = '215'
   UNION ALL
   SELECT   t.pkBenID,
       s2.pkSiteID,
       s2.ParentID,
       s2.Name,
       s2.Icon,
       s2.link
   FROM   Sites s2
   INNER JOIN t
   ON     s2.ParentID = t.pkSiteID
   WHERE   s2.pkSiteID != t.pkSiteID
     )
SELECT   t.*,
     f.*
FROM   t
LEFT JOIN SitesFunctions sf
ON     t.pkSiteID = sf.fkSiteID
LEFT JOIN Functions f
ON     sf.fkFuncID = f.pkFuncID
ORDER BY t.pkBenID,t.pkSiteID

Erst habe ich aus deiner Tabelle Users die Tabellen Users_Sites und Users_Roles gemacht. Ich würde dir raten das fest so vorzusehen, Gründe siehe Vorpost.

Dann joine ich die User und Rollen so, das ich weiß welcher User über die Rollen welchen Sites zugeordnet ist. Dazu verschmelze ich die direkten Users_Sites Zuordnungen und das Filtere ich noch auf Dubletten. So erhalte ich alle Users_Sites inklusive dem Umweg über die Rollen.

Dann der gewohnte Join, wie gehabt.

Ich würde noch Role_Sites in Roles_Sites umbennen und die Spalte RoldeID in RoleID.

logan517 · 30 August 2016

Hi

das funktioniert perfekt, vielen vielen Dank dafür

ukulele schrieb:
Ist eigentlich ganz einfach

Kann ich nun nicht bestätigen, aber wenn man sich den Code so durch liest, dann macht es schon sinn

Ich habe die Tabelle Users aufgeteilt in Users_Sites und Users_Roles und das entsprechend im Code geändert. Außerden habe ich unten beim letzten SELECT noch ein "WHERE pkFuncID IS NOT NULL" beigefügt, sodass ich nur vorhandene Funktionen bekomme, falls einer Seite keine zugeordnet sind.

ukulele schrieb:
und die Spalte RoldeID in RoleID.

ups, da hat sich wohl ein d eingeschleust und ist das Autovervollständigung vom ssms nicht aufgefallen

Grüße

JOIN innerhalb einer Tabelle

logan517

Benutzer

akretschmer

Datenbank-Guru

logan517

Benutzer

ukulele

Datenbank-Guru

logan517

Benutzer

Anhänge

ukulele

Datenbank-Guru

logan517

Benutzer

ukulele

Datenbank-Guru

logan517

Benutzer

Anhänge

ukulele

Datenbank-Guru

logan517

Benutzer

ukulele

Datenbank-Guru

logan517

Benutzer

ukulele

Datenbank-Guru

logan517

Benutzer

Ähnliche Themen