SELECT in einer n x m Relation mit mehreren Kriterien

[stryga42]

Ich habe eine n zu m Relation, sagen wir mal eine Tabelle image, die Bilder enthält, eine Tabelle label, die Schlagworte enthält, und eine img_lab, die die PKs von image und label miteinander verknüpft. Einem Bild dürfen 0..n label zugeordnet sein, sogar derselbe label mehrfach. (Sinnvoll, weil img_lab noch Zusatzattribute enthält - z.B. wo im Bild das Schlagwort zutrifft)
Ich will jetzt ein SELECT bauen, das nach images sucht, die eine bestimmte Kombination von labels tragen. Ich brauche zuerst mal AND verknüpfte labels. (von den labels sind schon die PKs bekannt, ich benötige also keinen JOIN auf die label Tabelle - angenommen die label PKs sind 42 und 99)

SELECT img.pk FROM image img INNER JOIN img_lab ixl ON img.pk=ixl.image WHERE ixl.label IN (42, 99) GROUP BY img.pk HAVING COUNT(1) = 2

Das funktioniert aber nur solange kein gesuchtes Schlagwort einem Bild mehrfach zugeordnet ist.
Fragen:

• Ist das grundsätzlich der richtige Zugang?
• Gibt es eine Funktion, die ich in der HAVING clause verwenden kann, und die nur überprüft, ob bestimmte Werte in der GROUP enthalten sind?
• Oder habt ihr eine andere Idee?

OR verknüpfte labels sollten einfacher sein, da funktioiert immer ...HAVING COUNT(1) > 1 - richtig oder übersehe ich da was?
Danke!

 

[dabadepdu]

Mit Having bist Du eigentlich richtig. Vielleicht spielt Dir die DB einen Streich.
Ohne eine ein konkretes Beispiel mit Tabellen und Daten ist mir das zuviel Kopfrechnen.

 

[stryga42]

Hier mit Beispieldaten:
use test; CREATE TABLE images ( pk INTEGER PRIMARY KEY, name TEXT ); CREATE TABLE labels ( pk INTEGER PRIMARY KEY, name TEXT ); CREATE TABLE img_lab ( pk INTEGER auto_increment PRIMARY KEY, image INTEGER NOT NULL, label INTEGER NOT NULL, stuff TEXT, FOREIGN KEY(label) REFERENCES labels(pk) ON DELETE RESTRICT, FOREIGN KEY(image) REFERENCES images(pk) ON DELETE CASCADE ); INSERT INTO images (pk,name) VALUES (1,'Meer'); INSERT INTO images (pk,name) VALUES (2,'Apfelbaum'); INSERT INTO images (pk,name) VALUES (3,'Szene'); INSERT INTO images (pk,name) VALUES (4,'Die große Leere'); INSERT INTO labels (pk,name) VALUES (1,'rot'); INSERT INTO labels (pk,name) VALUES (2,'grün'); INSERT INTO labels (pk,name) VALUES (3,'blau'); INSERT INTO img_lab (image,label,stuff) VALUES (1,3,'Wasser'); INSERT INTO img_lab (image,label,stuff) VALUES (1,3,'Himmel'); INSERT INTO img_lab (image,label,stuff) VALUES (2,2,'Blätter'); INSERT INTO img_lab (image,label,stuff) VALUES (2,1,'Apfel1'); INSERT INTO img_lab (image,label,stuff) VALUES (2,1,'Apfel2'); INSERT INTO img_lab (image,label,stuff) VALUES (3,1,'Auto'); INSERT INTO img_lab (image,label,stuff) VALUES (3,2,'Baum'); INSERT INTO img_lab (image,label,stuff) VALUES (3,3,'Himmel'); # suche nach rot UND grün sollte Apfelbaum und Szene ergeben, liefert aber nur Szene SELECT img.pk, img.name FROM images img INNER JOIN img_lab ixl ON img.pk=ixl.image WHERE ixl.label IN (1,2) GROUP BY img.pk HAVING COUNT(1)=2;

Wobei - beim Schreiben des Beispiels ist mir gerade eine Idee gekommen. Es gibt ja auch ein COUNT(DISTINCT ...)!

SELECT img.pk, img.name FROM images img INNER JOIN img_lab ixl ON img.pk=ixl.image WHERE ixl.label IN (1,2) GROUP BY img.pk HAVING COUNT(DISTINCT ixl.label)=2;

scheint zu funktionieren!