[MySQL] m:n Relation. Eine id mit bestimmten Kriterien bestimmen

[Sören]

Hallo,

Ein Beispiel meiner M:N Verbindingstabelle sieht wie folgt aus:

rightid  groupid
   1        1
   1        2
   1        3
   1        4
   2        1
   2        2
   3        1
   3        3
   4        2

Mein gewünschter Query lässt sich durch folgende Beispiele erklären:

Mein query soll mir nur EINE rightid zurückgeben, die mit groupid 1 und 2 nicht aber mit 3 und 4 (also alle anderen) verbunden ist. Das Ergbnis wäre in diesem Fall für rightid 2.

Mein query soll mir nur EINE rightid zurückgeben, die mit groupid 2 nicht aber mit 1, 3 und 4 (also alle anderen) verbunden ist. Das Ergbnis wäre in diesem Fall für rightid 4.

Mein query soll mir nur EINE rightid zurückgeben, die mit groupid 3 und 4 nicht aber mit 1 und 2 (also alle anderen) verbunden ist. Das Ergbnis wäre in diesem Fall für rightid NULL.

Wie lässt sich das realisieren?

Gruß Sören

[Frank (no reg)]

Hallo,

äh, alles in einer einzigen Abfrage?

Was hast du bis jetzt selbst zustande gebracht? Oder erwartest du, dass man dir hier das fertige Query auf einem Silbertablett serviert?

Ansonsten heissen die Stichworte für dich: DISTINCT, IN, UNION ALL
Wenn du irgendwo NULL sehen willst, dann käme wohl noch ein LEFT OUTER JOIN dazu.

Das mit "groupid" sieht etwas nach Permutation aus.

Ciao, Frank

[Sören]

Hallo

äh, alles in einer einzigen Abfrage?

Ja, die queries sollten sich nur in den verschieden angegebenen groupids unterscheiden.

Was hast du bis jetzt selbst zustande gebracht?

Tut mir leid das hatte ich vergessen zu schreiben:

Fürs erste Beispiel sieht mein query so aus:

SELECT rightid FROM group_to_right gtr WHERE EXISTS (SELECT 1 FROM group_to_right exists_gtr1, group_to_right exists_gtr2 WHERE gtr.rightid = exists_gtr1.rightid AND gtr.rightid = exists_gtr2.rightid AND exists_gtr1.groupid = 1 AND exists_gtr2.groupid = 2) AND NOT EXISTS (SELECT 1 FROM group_to_right not_exists_gtr1, group_to_right not_exists_gtr2 WHERE gtr.rightid = not_exists_gtr1.rightid AND gtr.rightid = not_exists_gtr2.rightid AND not_exists_gtr1.groupid = 3 AND not_exists_gtr2.groupid = 4) LIMIT 0,1

Ist jedoch etwas komplex, aber sollte funktionieren.
Das ist mein bisheriger Ansatz, jedoch denke ich nicht, dass der query effizient ist. Zudem muss ich den mühselig mit php zusammenbauen.

Gibt es da eine einfachere und effizienter variante?

Ansonsten heissen die Stichworte für dich: DISTINCT, IN, UNION ALL

Danke werde ich mir mal anschauen.

Wenn du irgendwo NULL sehen willst, dann käme wohl noch ein LEFT OUTER JOIN dazu.

Tut mir leid habe mich vertan, mit NULL meinte ich einen leeren Datensatz

Das mit "groupid" sieht etwas nach Permutation aus.

Ist das nicht bei jeder m:n relation so?

Gruß Sören

[Frank (no reg)]

Hallo nochmals,

versuche doch vielleicht mal dein SQL etwas zu formatieren/strukturieren, so mit mehreren Zeilen und Einrückungen und so ...

Mit "Permutationen" meinte ich, ob du für die Kombinationen von groupids fixe Daten, die du prüfen willst oder ob du jedes Kombination von Group-IDs prüfen willst?

Tut mir leid habe mich vertan, mit NULL meinte ich einen leeren Datensatz

Leerer Datensätze sind Datensätze mit NULL für jede Spalte. ?? Möchtest du also einen Ergebnisdatensatz (Record) ausgegeben haben, wenn es für eine der gewünschten Kombinationen aus groupids keine Entsprechung bei rightid gibt?

Cheers, Frank

[Sören]

Hallo,

SELECT rightid FROM group_to_right gtr  
WHERE EXISTS  
(  
    SELECT 1 FROM group_to_right exists_gtr1, group_to_right exists_gtr2  
    WHERE gtr.rightid = exists_gtr1.rightid AND gtr.rightid = exists_gtr2.rightid  
    AND exists_gtr1.groupid = 1 AND exists_gtr2.groupid = 2  
)  
AND NOT EXISTS  
(  
    SELECT 1 FROM group_to_right not_exists_gtr1, group_to_right not_exists_gtr2  
    WHERE gtr.rightid = not_exists_gtr1.rightid AND gtr.rightid = not_exists_gtr2.rightid  
    AND not_exists_gtr1.groupid = 3 AND not_exists_gtr2.groupid = 4  
)  
LIMIT 0,1

Mit "Permutationen" meinte ich, ob du für die Kombinationen von groupids fixe Daten, die du prüfen willst oder ob du jedes Kombination von Group-IDs prüfen willst?

Wenn ich das richtig verstehe, dann ja ich habe fixe Daten von groupids
Meine php funktion sieht nacher so aus:

function get_rightid_by_groups($groups = array()) {
    // $groups für den sql befehl formatieren
    // query ausführen um rightid zu bekommen
    // falls es keine rightid für die gewünschte Kombination von groupids gibt dann neu erstellen.
    // rightid ausgeben.
}

Dabei ist $groups die liste aller $groupids, die mit einer rightid verbunden sein sollen. Alle anderen möglichen groupids die nicht in $groups enthalten sind sollen nicht mit der rightid verbunden sein.
Daher EXISTS und NOT EXISTS

Leerer Datensätze sind Datensätze mit NULL für jede Spalte. ?? Möchtest du also einen Ergebnisdatensatz (Record) ausgegeben haben, wenn es für eine der gewünschten Kombinationen aus groupids keine Entsprechung bei rightid gibt?

Richtig!

Gruß Sören

[ChrisB]

Hi,

Mein query soll mir nur EINE rightid zurückgeben, die mit groupid 1 und 2 nicht aber mit 3 und 4 (also alle anderen) verbunden ist. Das Ergbnis wäre in diesem Fall für rightid 2.

Mein query soll mir nur EINE rightid zurückgeben, die mit groupid 2 nicht aber mit 1, 3 und 4 (also alle anderen) verbunden ist. Das Ergbnis wäre in diesem Fall für rightid 4.

Mein query soll mir nur EINE rightid zurückgeben, die mit groupid 3 und 4 nicht aber mit 1 und 2 (also alle anderen) verbunden ist. Das Ergbnis wäre in diesem Fall für rightid NULL.

Wie lässt sich das realisieren?

Mit EINE meinst du, dass du jeweils nur an einer rightid interessiert bist, selbst wenn mehrere die Bedingung erfüllen sollten? Das liesse sich zwar mittels LIMIT leicht machen - allerdings solltest du dann auch noch eine Sortierung definieren, um reproduzierbare Ergebnisse zu bekommen.

Das Vorhaben lässt sich umsetzen, in dem du mittels IF entscheidest, ob die groupid eine der geforderten ist. Wenn ja, den Wert 1 zurückgeben, wenn nein den Wert -1.
Diese Werte aufsummieren (nach rightid gruppiert), und dann per HAVING nur die Datensätze auswählen, bei denen diese Summe der Anzahl der geforderten groupids entspricht. (Im ersten Beispiel also 2, im zweiten 1.)

Nur das dritte Beispiel wird dadurch leicht von der Anforderung abweichend behandelt - du bekommst damit keinen Datensatz mit rightid = NULL zurück, sondern einfach keine Datensätze. Das sollte m.E. vermutlich die Applikation selber entsprechend handeln können.

MfG ChrisB

[Sören]

Hi,

Mit EINE meinst du, dass du jeweils nur an einer rightid interessiert bist, selbst wenn mehrere die Bedingung erfüllen sollten? Das liesse sich zwar mittels LIMIT leicht machen - allerdings solltest du dann auch noch eine Sortierung definieren, um reproduzierbare Ergebnisse zu bekommen.

Nein!
Nur ein Datensatz (Record) kann alle Bedingungen erfüllen.

Zum Verständnis:

Ein Recht ist verschiedenen Gruppen zugeordnet, aber nicht unbedingt allen Gruppen.
Wenn man nun ein Recht angibt, ist es einfach alle Gruppen, die diesem Recht zugeordnet sind, auszugeben.
Nun möchte ich das aber andersrum machen. Ich gebe mehrere Gruppen an und möchte das entsprechende Recht haben.
Existiert kein Recht für diese Gruppen, dann erstelle ich ein neues Recht.
Dadurch möchte ich verhindern, dass sowas ensteht:

rightid  groupid
   1        1
   1        2
   2        1
   2        2
   3        1
   3        2

Rechte 2 und 3 sind im Prinzip unnötig, da es schon ein Recht mit der rightid 1 gibt, das auf die Gruppen 1 und 2 verweist.

Das Vorhaben lässt sich umsetzen, in dem du mittels IF entscheidest, ob die groupid eine der geforderten ist. Wenn ja, den Wert 1 zurückgeben, wenn nein den Wert -1.
Diese Werte aufsummieren (nach rightid gruppiert), und dann per HAVING nur die Datensätze auswählen, bei denen diese Summe der Anzahl der geforderten groupids entspricht. (Im ersten Beispiel also 2, im zweiten 1.)

Das wäre in der Tat eine denkbare Lösung, jedoch wüsste ich nicht wie ich die realisieren sollte.
Ist das denn nicht einfach mit EXISTS und NOT EXISTS zu lösen?

Nur das dritte Beispiel wird dadurch leicht von der Anforderung abweichend behandelt - du bekommst damit keinen Datensatz mit rightid = NULL zurück, sondern einfach keine Datensätze.

Ja ich meinte auch einfach einen leeren Datensatz!

Gruß Sören

[ChrisB]

Hi,

Das Vorhaben lässt sich umsetzen, in dem du mittels IF entscheidest, ob die groupid eine der geforderten ist. Wenn ja, den Wert 1 zurückgeben, wenn nein den Wert -1.
Diese Werte aufsummieren (nach rightid gruppiert), und dann per HAVING nur die Datensätze auswählen, bei denen diese Summe der Anzahl der geforderten groupids entspricht. (Im ersten Beispiel also 2, im zweiten 1.)

Das wäre in der Tat eine denkbare Lösung, jedoch wüsste ich nicht wie ich die realisieren sollte.

Ähm - genau so, wie ich es gerade beschrieben habe?

Das war doch schon fast SQL - alle wichtigen Stichworte sind bereits enthalten.

Ja ich meinte auch einfach einen leeren Datensatz!

Ja, das antwortestest du Frank ja auch schon - trotzdem meinen wir etwas anderes, nämlich *keinen* Datensatz im Gegensatz zu einem „leeren”.

MfG ChrisB

[Sören]

Ähm - genau so, wie ich es gerade beschrieben habe?

Haha, ja ich hab das nun mal umgesetzt und ich hätte wirklich nicht gedacht, dass es so einfach ist!
Sehr einfallsreich! Vielen Dank =)

Hier mein Resultat:
SELECT rightid, IF(groupid = 3 OR groupid = 2,1,-1) as num_groups FROM group_to_right GROUP BY rightid HAVING SUM(num_groups) = 2

Gruß Sören

[ChrisB]

Hi,

Hier mein Resultat:
SELECT rightid, IF(groupid = 3 OR groupid = 2,1,-1) as num_groups FROM group_to_right GROUP BY rightid HAVING SUM(num_groups) = 2

Das IF kann man noch gleich ins SUM hinter HAVING verlagern (denn als Ergebnisspalte braucht man den Wert ja nicht); und statt mehrere Einzelbedingungen mittels OR zu vergleichen, würde ich den IN-Operator vorschlagen (damit generiert sich die Bedingung im Script noch simpler dynamisch, wenn die gruopids bspw. in einem Array vorliegen).

MfG ChrisB