Komplizierte SQL-Abfrage mit JOIN und IN

[Benjamin]

Hallo Community,

also zuerst einmal meine SQL-Abfrage:

"SELECT DISTINCT [...] FROM information
JOIN tagged ON tagged.infoId = information.id
JOIN tags ON tags.id = tagged.tagId
WHERE tags.tag IN ('keyword1', 'keyword2')"

Der Aufbau der Datenbank sieht so aus:
In der Tabelle "information" sind Informationen gespeichert.
In der Tabelle "tagged" ist eine ID zu einer Information und eine ID zu einem Tag gespeichert.
In der Tabelle "tags" sind die Tags gespeichert.
Jede Information kann also mehreren Tags zugewiesen werden über die Tabelle "tagged".

Die Abfrage soll folgendes machen: keyword1 und keyword2 (in der IN-Anweisung) werden gegeben und es sollen alle Informationen abgefragt werden, die mit beiden Tags verbunden sind. Ich denke, dass die JOINs gut klappen, aber mit dem IN ist irgendetwas falsch. Derzeit werden alle Einträge abgefragt, die mit mindestens einem der Keywords verbunden sind. Dabei soll die Abfrage auch mit mehreren Keywords funktionieren.

Ich hoffe, dass die Erklärung einigermaßen verständlich war und freue mich auf Antworten.

MfG,
Benjamin

[Vinzenz Mai]

Hallo

also zuerst einmal meine SQL-Abfrage:

"SELECT DISTINCT [...] FROM information
JOIN tagged ON tagged.infoId = information.id
JOIN tags ON tags.id = tagged.tagId
WHERE tags.tag IN ('keyword1', 'keyword2')"

Die Abfrage soll folgendes machen: keyword1 und keyword2 (in der IN-Anweisung) werden gegeben und es sollen alle Informationen abgefragt werden, die mit beiden Tags verbunden sind. Ich denke, dass die JOINs gut klappen, aber mit dem IN ist irgendetwas falsch. Derzeit werden alle Einträge abgefragt, die mit mindestens einem der Keywords verbunden sind. Dabei soll die Abfrage auch mit mehreren Keywords funktionieren.

Überprüfe in einem Subselect, ob die Anzahl der gefundenen Einträge zu einer id (die es in Deiner Spaltenliste geben muss), genau zwei (d.h. der Anzahl der Keywords) ist.

Freundliche Grüße

Vinzenz

[Benjamin]

Das habe ich gerade mal ausprobiert. Leider klappt das so nicht. Folgendes Beispiel:

Ich frage ab nach den Keywords "key1" und "key2".

Nun streicht der schon mal alle weg die nur ein Keyword haben oder mehr als 2. Das ist ein Fortschritt. Leider werden aber auch Einträge abgeholt, die zum Beispiel die Keywords "key1" und "key3" haben, da einer des Keywords übereinstimmt.

Mein Subselect sieht so aus:

"AND (SELECT COUNT(*) FROM tagged WHERE tagged.infoId = information.id) = 2"

Grüße!

[Vinzenz Mai]

Hallo

Das habe ich gerade mal ausprobiert. Leider klappt das so nicht.

Mein Subselect sieht so aus:

"AND (SELECT COUNT(*) FROM tagged WHERE tagged.infoId = information.id) = 2"

Nein, im Subselect musst Du exakt das gleiche abfragen wie außen :-)
Ermittle, wieviele Datensätze Deiner bisherigen Abfrage es zu jeder id gibt.
Gibt es zwei, dann ist - wegen DISTINCT - alles in Ordnung. Das setzt außerdem noch voraus, dass Du auch die Tag-Spalte in Deiner Abfrage drin hast.

Freundliche Grüße

Vinzenz

[Vinzenz Mai]

Hallo Benjamin,

Das habe ich gerade mal ausprobiert. Leider klappt das so nicht.

Du könntest wie folgt vorgehen:

1. Schritt:
Welchen ids sind überhaupt die gesuchten Tags zugeordnet?

  
SELECT DISTINCT       -- Gib mir die eindeutigen  
    i.id AS id,       -- ids aus der Tabelle information  
    tags.tag          -- und zugeordneten tags aus der Tabelle tags  
FROM  
    information i     -- wobei die Tabelle information  
INNER JOIN            -- mit der Tabelle  
    tagged t          -- tagged  
ON t.infoId = i.id    -- über die Felder id (information) bzw. infoId  
                      -- (tagged) verknüpft ist und die Tabelle tagged  
INNER JOIN            -- mit der Tabelle  
    tags              -- tags  
ON                    -- über die Felder tagId (tagged) und id (tags)  
    tags.id = t.tagId -- verknüpft ist  
WHERE                 -- wobei mich nur die Tags aus  
    tags.tag IN ('keyword1', 'keyword2') -- vorgegebener Liste interessieren  

2. Schritt:
Zähle, wie oft welche id vorkommt und filtere nur die aus,
die zweimal vorkommen.

  
SELECT  
    s.id                  -- Wähle aus dem Ergebnis obiger Abfrage, die  
                          -- mit dem Namen s angesprochen wird, die ids  
                          -- aus, die ...  
FROM (  
    SELECT DISTINCT  
        i.id AS id,  
     tags.tag  
    FROM  
        information i  
    INNER JOIN  
        tagged t  
    ON  
        t.infoId = i.id  
    INNER JOIN  
        tags  
    ON  
        tags.id = t.tagId  
    WHERE  
        tags.tag IN ('keyword1', 'keyword2')  
) s                       -- Festlegung des Aliasnamens für die Abfrage  
GROUP BY  
    s.id  
HAVING COUNT(s.id) = 2    -- ... genau zweimal vorkommen  

Du kannst problemlos diese Abfrage um alle Spalten erweitern, die eindeutig
von s.id abhängen (und nach diesen Feldern gruppieren).

Freundliche Grüße

Vinzenz

[dedlfix]

echo $begrüßung;

Ich hoffe, dass die Erklärung einigermaßen verständlich war und freue mich auf Antworten.

Es fehlt die Angabe des DBMS und dessen Version. Ich gehe im Folgenden von MySQL mit einer Version größer oder gleich 4.1 aus.

Die Abfrage soll folgendes machen: keyword1 und keyword2 (in der IN-Anweisung) werden gegeben und es sollen alle Informationen abgefragt werden, die mit beiden Tags verbunden sind.

Die Funktion GROUP_CONCAT() macht aus vielen Zeilen abhängig vom Gruppierungsparameter einen einzelnen Wert. Du könntest damit nach der information.id gruppiert alle Tags aneinanderhängen. Das muss so erfolgen, dass die Werte einen Set-ähnlichen String ergeben, also kommagetrennt aneinanderhängen. Dieses "Set" kannst du dann mit FIND_IN_SET() durchsuchen: WHERE FIND_IN_SET(tag1, set) AND FIND_IN_SET(tag2, set)

Allerdings ist das sicher nicht sehr effektiv, weil hier Stringoperationen stattfinden, die man nicht mit Index oder ähnlichen Maßnahmen unterstützen kann. Der Vorschlag ist also nur für überschaubare Datenmengen geeignet.

echo "$verabschiedung $name";