wheri in all

[Nick]

Wie kann man in einer n:m-Relation alle Datensätze abfragen, in denen Spalte a x Zuordnungen zu b hat?

Tabelle

a b
1 1
1 3
1 4
1 5
2 1
2 5
3 1
3 3
4 3
4 4
5 1
5 3
5 4
5 7

Hier z. B.: Suche alle a (oder Zeilen), für die gilt: b ist 1|3|4, wobei a alle b erfüllen muss.

Hier trifft das auf a=1 und a=5 zu.

Nicht korrekt, aber zur Verdeutlichung:

select * from Tabelle where b in all (1,3,4)

Das heißt, in soll nicht als oder sondern als und ausgeführt werden, was jedoch funktioniert.

Wie ist der richtige Weg?

[Jens Holzkämper]

Tach,

select * from Tabelle where b in all (1,3,4)

Das heißt, in soll nicht als oder sondern als und ausgeführt werden, was jedoch funktioniert.

Wie ist der richtige Weg?

Idee, sofern die Zuordnungen unique sind:
SELECT a FROM Tabelle WHERE b IN (1,3,4) GROUP BY a HAVING COUNT(a)=3

mfg
Woodfighter

[Nick]

Idee, sofern die Zuordnungen unique sind:
SELECT a FROM Tabelle WHERE b IN (1,3,4) GROUP BY a HAVING COUNT(a)=3

Die Idee mit count() hatte ich auch, aber nicht als Bedingung in having, sondern als select count. Damit kam ich nicht weiter, außer dass ich wusste, wie oft jeweils ein a vorkam. Dein Weg funktioniert, ich bin da wohl etwas aus SQL raus, daher vielen Dank.

Um noch in der verknüpften Tabelle an die Datensätze mit a zu kommen, benötigt man noch so etwas:

select * from vTabelle where a in (SELECT a FROM Tabelle WHERE b IN (1,3,4) GROUP BY a HAVING COUNT(a)=3)

Das war mein zweiter Fehler, ich hatte nicht in, sondern =. Das eine nennt man wohl skalar, das andere vektoriell.

So geht es jetzt jedenfalls, ich empfinde die Lösung allerdings als unsauber, vielleicht gibt es noch eine andere.

Noch eine Frage. Wenn man in() dynamisch befüllt, so funktioniert das nur, wenn mindestens ein Argument an in() übergeben wird, sonst kommt es zu einem Fehler. Das ist relativ mühsam bei der Programmierung zu beachten. Gibt es da noch eine andere Lösung? Vielleicht etwas mit if? Intuitiv dächte ich, in(), also leer, sollte kein Problem sein, liefert eben keine Ergebnisse, aber SQL sieht das anders.

In die ähnliche Richtung ginge noch die Frage, wie man die 3 aus count(a) mit SQL selbst ermittelt, wenn das überhaupt funktioniert

[dedlfix]

Tach!

Noch eine Frage. Wenn man in() dynamisch befüllt, so funktioniert das nur, wenn mindestens ein Argument an in() übergeben wird, sonst kommt es zu einem Fehler. Das ist relativ mühsam bei der Programmierung zu beachten.

Es ist sinnlos, das DBMS nach nichts zu befragen. Also solltest du das vorher feststellen, wenn es gar keinen Grund zum Suchen gibt. Das ist eine Plausibilitätsprüfung und solche sind immer mühsam aber unvermeidlich.

In die ähnliche Richtung ginge noch die Frage, wie man die 3 aus count(a) mit SQL selbst ermittelt, wenn das überhaupt funktioniert

Die Frage war doch, dass du wissen möchtest, wenn x Zuordnungen vorhanden sind. Heißt das, dass dieses x mit einer Abfrage ermittelt werden kann, die einen skalaren Wert liefert? Dann: Subquery (oder Stored Function).

dedlfix.

[Nick]

Tach!

Noch eine Frage. Wenn man in() dynamisch befüllt, so funktioniert das nur, wenn mindestens ein Argument an in() übergeben wird, sonst kommt es zu einem Fehler. Das ist relativ mühsam bei der Programmierung zu beachten.

Es ist sinnlos, das DBMS nach nichts zu befragen. Also solltest du das vorher feststellen, wenn es gar keinen Grund zum Suchen gibt. Das ist eine Plausibilitätsprüfung und solche sind immer mühsam aber unvermeidlich.

Wie gesagt ich hielte es intuitiv für nicht abwegig. Es ist ja auch eine Information, wenn man nach in() leer sucht, aber wenn es nicht funktioniert, kann man es eben nur so hinnehmen.

In die ähnliche Richtung ginge noch die Frage, wie man die 3 aus count(a) mit SQL selbst ermittelt, wenn das überhaupt funktioniert

Die Frage war doch, dass du wissen möchtest, wenn x Zuordnungen vorhanden sind. Heißt das, dass dieses x mit einer Abfrage ermittelt werden kann, die einen skalaren Wert liefert? Dann: Subquery (oder Stored Function).

Die Daten für b kommen von außen und werden in die Abfrage in() injiziert, wenn das Deine Frage ist. So kann ich, nach meinem Wissen, auch nur außerhalb von SQL zählen, wie viel b auf a zutreffen müssen. Ich denke, anders geht es nicht, ist nur eine kleine Hoffnung, dass es doch anders funktioniert, wenn nicht, dann eben, wie Du schreibst, mühsam, aber unvermeidlich. Mehr noch, und das ist eigentlich wichtiger als die Schreibarbeit, es ist relativ unübersichtlich, wenn es sich nicht nur um eine Tabelle handelt, sondern um viele, die in der Weise, andere auch anders, verknüpft werden. Bei zehn und mehr verknüpften Tabellen ist die Abfrage nicht mehr wirklich zu erfassen, wenn die Stringbildung dauernd unterbrochen werden muss, um von außen etwas zu injizieren, das womöglich noch, von Bedingungen abhängig, mal in die Abfrage kommt, mal nicht. Da bin ich immer froh, wenn man gewisse Dinge mit SQL selbst erledigen kann.

[dedlfix]

Tach!

Bei zehn und mehr verknüpften Tabellen ist die Abfrage nicht mehr wirklich zu erfassen, wenn die Stringbildung dauernd unterbrochen werden muss, um von außen etwas zu injizieren, das womöglich noch, von Bedingungen abhängig, mal in die Abfrage kommt, mal nicht. Da bin ich immer froh, wenn man gewisse Dinge mit SQL selbst erledigen kann.

Das wird da auch nicht zwangsläufig übersichtlicher, wenn das Thema komplex ist. Die Stringbildung muss man übrigens nicht unbedingt unterbrechen. Es gibt sprintf().

dedlfix.

[Nick]

Das wird da auch nicht zwangsläufig übersichtlicher, wenn das Thema komplex ist. Die Stringbildung muss man übrigens nicht unbedingt unterbrechen. Es gibt sprintf().

Ich weiß, die Funktion hat aber auch ihre Grenzen, gerade in Hinsicht auf die Übersichtlichkeit (Beispiel: ein Präfix vor Tabellen) finde ich es manchmal besser, eben doch die Stringbildung zu unterbrechen, vielleicht empfinden das andere ja auch anders.

Es ist aber gut zu hören, dass andere auch mit solchen Widrigkeiten "kämpfen".

Eine andere Lösung als in() + having count() kennst Du ebenfalls bei der Problemstellung nicht?

[dedlfix]

Tach!

Ich weiß, die Funktion [sprintf] hat aber auch ihre Grenzen, gerade in Hinsicht auf die Übersichtlichkeit (Beispiel: ein Präfix vor Tabellen) finde ich es manchmal besser, eben doch die Stringbildung zu unterbrechen, vielleicht empfinden das andere ja auch anders.

Wo ist denn beim Präfix die Grenze? Zur Not gibt es %1$s, was man wiederholt verwendet und den Wert nur einmal als Parameter hat. Abgesehen davon, gibt es vielleicht bessere Lösungen, wie Suchen und Ersetzen einer markanten Zeichenfolge (bevor die Werte eingefügt werden, in denen kollidierende Zeichenfolgen enthalten sein können).

Eine andere Lösung als in() + having count() kennst Du ebenfalls bei der Problemstellung nicht?

Du willst das ja auch noch flexibel haben, also egal wieviele Werte. Eine Lösung, bei der pro Wert ein großer Codezuwachs entsteht, ist also nicht optimal. Die HAVING-COUNT-Lösung ist da schon die beste, die mir einfällt.

Es geht aber noch komplizierter:

SELECT a FROM tabelle WHERE b in (werte) GROUP BY a HAVING GROUP_CONCAT(b ORDER BY b) = 'werte_als_kommaseparierter_string'

Das ist aber nicht schön, weil für jede Gruppe das GROUP_CONCAT berechnet werden muss, anstatt nur die Anzahl der Gruppenwerte zu vergleichen.

Das Problem ist, dass deine dich interessierenden Werte nicht "nebeneinander" sondern "untereinander" stehen und erst zusammengefasst werden müssen, bevor du sie auswerten kannst. SQL ist dafür nicht ausgelegt, in Abhängigkeit von anderen Datensätzen derselben Tabelle zu agieren. Da müssen dann solche Hilfsmittel wie Gruppierungen oder Selfjoins herhalten.

dedlfix.

[hotti]

hi,

join mit sich selbst:

  
SELECT  
  
x.a  
  
FROM tab x  
join tab y using(a)  
join tab z using(a)  
where x.b = 1 and y.b = 3 and z.b = 4  
  

Ergebnis:
$VAR1 = [
          {
            'a' => '1'
          },
          {
            'a' => '5'
          }
        ];

Hotti

[Nick]

hi,

join mit sich selbst:

SELECT

x.a

FROM tab x
join tab y using(a)
join tab z using(a)
where x.b = 1 and y.b = 3 and z.b = 4

  
Das ist sehr interessant. Allerdings denke ich, es ist bei der dynamischen Abfrage eher schlechter als die andere Lösung, da man hier, so würde ich es machen, mit zwei Schleifen die Unterabfrage aufbauen müsste (die zwei joins, wo man so etwas wie join tab as x\_i bräuchte, und die Bedingungen x\_0 = 1 and x\_1 = 3 etc. Ich denke, das ist sehr unübersichtlich, aber für nichtdynamische Sachen ist es auf jeden Fall (be)merkenswert, auch dafür danke.  

[hotti]

hi,

Das ist sehr interessant.

Finde ich auch ;)
Was dahinter steckt ist die Normalisierung (jaja, immer wieder dasselbe, danke Herr Codd).
Allgemein: Bei einer 1:n Beziehung braucht es zwei Tabellen. Bei einer n:m Beziehung braucht es eine Tabelle mehr, also drei.

Mit dem SELF-Join erzeugst Du diese 3 Tabellen, die referentielle Integrität läuft dazu über das Feld 'a' und die Bedingungen in der Whereklause können nun mit AND verknüpft werden, weil drei Tabellen gegeben sind.

Allerdings denke ich, es ist bei der dynamischen Abfrage eher schlechter als die andere Lösung, da man hier, so würde ich es machen, mit zwei Schleifen die Unterabfrage aufbauen müsste (die zwei joins, wo man so etwas wie join tab as x_i bräuchte, und die Bedingungen x_0 = 1 and x_1 = 3 etc. Ich denke, das ist sehr unübersichtlich, aber für nichtdynamische Sachen ist es auf jeden Fall (be)merkenswert, auch dafür danke.

Das wäre zu überlegen, das liegt an Dir ;)

Hotti

[dedlfix]

Tach!

Das ist sehr interessant.

Auf den ersten Blick vielleicht. Sie offenbart ihre Nachteile, wenn noch mehr Werte als drei gesucht werden. Es werden nämlich n-1 Selfjoins und n Bedingungen benötigt. Man muss das Statement zwingend anhand dieser Gegebenheiten zusammenbauen. Die HAVING-COUNT-Lösung käme auch mit Subquerys für die Werte in IN() und die Anzahl aus.

Das nächste Problem wird sein, dass hier eine Zwischenmenge aus sehr vielen Datensätzen gebaut wird. Für jeden Wert von a werden count(a) hoch Anzahl(Suchwerte) Zeilen erzeugt. Für den gegebenen Fall sind das bei a=1 und 5 jeweils 4^3 = 64 Zeilen, bei 2, 3 und 4 jeweils 2^3 = 8. Macht bei den wenigen Beispieldaten schon 152 Zeilen. Ein Suchbegriff mehr macht 544 und noch einer sind schon 2112 Zeilen.

Und das wird sicher nicht besser, wenn die Query gegen die echten Daten laufen galassen wird. Bei kleinen Tabellengrößen mag das noch gehen, bei großen wird es immer ungünstiger. Was du aber noch machen kannst: die Query noch komplexer, indem du zu jeder Join-Bedingung das schon bekannte IN() mit den Suchwerten hinzufügst, was die Anzahl der möglichen Kombinationen von b einschränkt.

Was dahinter steckt ist die Normalisierung (jaja, immer wieder dasselbe, danke Herr Codd).
Allgemein: Bei einer 1:n Beziehung braucht es zwei Tabellen. Bei einer n:m Beziehung braucht es eine Tabelle mehr, also drei.

Diese Begründung passt nicht. Es geht hier lediglich um die Beziehungstabelle. Für das eigentliche Problem ist nicht weiter relevant, dass sie eine solche ist, ebensowenig wie ihre Beziehungen zu anderen Tabellen. Von den anderen Tabellen könnte lediglich eine ins Spiel kommen, wenn es darum geht, die Suchwerte direkt statt ihrer IDs anzugeben. Dann könnte man die beiden Tabellen mit einen Join verbinden, das IN() in die zweite Tabelle verlagern, dort die eigentlichen Suchwerte anzugeben und damit statt mit deren IDs in der jetzigen Tabelle zu suchen. Das löst nur das grundlegende Problem nicht. Es fügt nur noch viel mehr Komplexität hinzu, weil die b-Tabelle zu jedem Self-Join hinzugejoint werden muss.

Mit dem SELF-Join erzeugst Du diese 3 Tabellen, die referentielle Integrität läuft dazu über das Feld 'a' und die Bedingungen in der Whereklause können nun mit AND verknüpft werden, weil drei Tabellen gegeben sind.

Fachwort-Bullshit-Bingo. "Referenzielle Integrität" ist, wenn die IDs in a und b auf Gegenstücke in den anderen Tabellen zeigen und nicht in der Luft hängen. Das hat mit dem vorliegenden Problem nichts weiter zu tun. Sie kommt erst ins Spiel, wenn die Gegenstücke zu a und b benötigt werden.

Die Zahl 3 ist nur zufällig dieselbe bei "Anzahl der Tabellen einer n:m-Beziehung" und der Anzahl der hier benötigten Selfjoin-Tabellen. Letztere korreliert mit der Anzahl der Suchbegriffe, ist also eine beliebige Zahl. "Diese drei Tabellen" sind letztlich nur eine von den drei m:n-Tabellen, diese wird jedoch weniger, gleich oder mehr als dreimal verwendet.

Die Bedingungen "können nun mit AND verknüpft werden", weil du durch die Selfjoins die "senkrechten" Daten in die "Waagerechte" gebracht hast. Mit den oben genannten Nachteilen der Datensatzvervielfältigung dieser Variante.

Allerdings denke ich, es ist bei der dynamischen Abfrage eher schlechter als die andere Lösung, da man hier, so würde ich es machen, mit zwei Schleifen die Unterabfrage aufbauen müsste (die zwei joins, wo man so etwas wie join tab as x_i bräuchte, und die Bedingungen x_0 = 1 and x_1 = 3 etc. Ich denke, das ist sehr unübersichtlich, aber für nichtdynamische Sachen ist es auf jeden Fall (be)merkenswert, auch dafür danke.

So ist es. Das Statement ist viel umständlicher zu erzeugen und mit mehr Nachteilen als die HAVING-COUNT-Lösung behaftet.

dedlfix.

[hotti]

hi,

Für jeden Wert von a werden count(a) hoch Anzahl(Suchwerte) Zeilen erzeugt. Für den gegebenen Fall sind das bei a=1 und 5 jeweils 4^3 = 64 Zeilen, bei 2, 3 und 4 jeweils 2^3 = 8. Macht bei den wenigen Beispieldaten schon 152 Zeilen. Ein Suchbegriff mehr macht 544 und noch einer sind schon 2112 Zeilen.

Setze mal auf b einen Index und lass Dir den Join einschließlich der where-Klause mit explain ausgeben.

Hotti