Count zählt falsch

[Vinzenz Mai]

Hallo,

Folgende Ausgangssituation habe ich:
1 Geschichte von user 8 und den Usern 1,2,3 und 4 als Co-Autoren und 1 User (uid 7) hat die Geschichte in seinen Favs. Ich verwende folgenden Select um dem aktuellen User (das kann der Autor ABER auch einer der Co-Autoren sein) die Anzahl der User anzuzeigen, die eine seiner Geschichten als Favs hat.

Story:

|sid|uid| title              | validated
-----------------------------+------------
| 1 | 8 | Beispielgeschichte | 1

CoAuthors:

|sid|uid|

| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 1 | 4 |

Favoriten:

|item|uid| fstype

| 1  | 7 | ST

SELECT s.title, s.sid, count(fs.item) as fscount FROM Story as s LEFT JOIN Favoriten as fs ON s.sid = fs.item AND fs.type = 'ST' LEFT JOIN CoAutoren AS c ON s.sid = c.sid WHERE (s.uid = '8' OR c.uid = '8') AND s.validated > 0 GROUP by fs.sid

Aus Deiner Abfrage schließe ich, dass Du MySQL verwendest. Jedes andere DBMS gibt Dir kein Ergebnis, sondern eine Fehlermeldung zurück, weil Du nicht nach allen Spalten gruppierst, auf die Du keine Aggregatsfunktion anwendest.

Wenn ich diesen SQL ausführe, erhalte ich immer 4 im Feld fscount. Es sollte aber nur 1 drinstehen.

Nein, es sollte 4 drin stehen. Das heißt, Deine Abfrage muss anders gestaltet werden, damit sie Dir das gewünschte Ergebnis, die 1 liefert.

Ich versuche Dir Deine Abfrage zu erläutern, indem ich sie Schritt für Schritt aufbaue:

SELECT  
    s.title,  
    s.sid  
FROM  
    Story s

liefert mit Deinen Beispieldaten (die ich um einen Titel ergänzt habe):

title              | sid
-------------------+----
Beispielgeschichte |  1

zurück.

Im nächsten Schritt joinst Du die Favoriten hinzu. Da Du auch Ergebnisse - nämlich die 0 - haben willst, wenn noch keiner die Story bei seinen Favoriten hat, verwendest Du einen LEFT JOIN.

SELECT  
    s.title,  
    s.sid,  
    fs.item                  -- über das soll später gezählt werden  
FROM  
    Story s  
LEFT JOIN  
    Favoriten fs  
ON  
    s.sid = fs.item AND fs.type = 'ST'

Ergebnis:

title              | sid | item
-------------------+-----+------
Beispielgeschichte |  1  |   1

Im dritten Schritt joinst Du die Co-Autoren dazu. Da jemand eine Story auch alleine verfasst haben kann, sieht der LEFT JOIN angemessen aus - hier begehst Du allerdings den Fehler, der zu dem von Dir nicht gewünschten Ergebnis führt (siehe weiter unten):

SELECT  
    s.title,  
    s.sid,  
    fs.item                  -- über das soll später gezählt werden  
FROM  
    Story s  
LEFT JOIN  
    Favoriten fs  
ON  
    s.sid = fs.item AND fs.type = 'ST'  
LEFT JOIN  
    CoAutoren c  
ON  
    s.sid = c.sid

Da jeder der Datensätze in der Tabelle CoAutoren passt, sieht das Ergebnis wie folgt aus:

title              | sid | item
-------------------+-----+------
Beispielgeschichte |  1  |   1
Beispielgeschichte |  1  |   1
Beispielgeschichte |  1  |   1
Beispielgeschichte |  1  |   1

Du hast nun vier Datensätze, die alle gleich sind. Bauen wir nun die WHERE-Klausel ein:

SELECT  
    s.title,  
    s.sid,  
    fs.item                  -- über das soll später gezählt werden  
FROM  
    Story s  
LEFT JOIN  
    Favoriten fs  
ON  
    s.sid = fs.item AND fs.type = 'ST'  
LEFT JOIN  
    CoAutoren c  
ON  
    s.sid = c.sid  
WHERE  
    (s.uid = '8' OR c.uid = '8')  
    AND s.validated > 0

Da für jeden Datensatz gilt, dass s.uid = 8 ist und validated größer als 0 ist, bleiben alle Datensätze in der Ergebnismenge:

title              | sid | item
-------------------+-----+------
Beispielgeschichte |  1  |   1
Beispielgeschichte |  1  |   1
Beispielgeschichte |  1  |   1
Beispielgeschichte |  1  |   1

Du solltest sehen, dass beim Zählen der item-Werte 4 herauskommt.

Was Du haben willst, sind nicht alle Autoren oder Co-Autoren aller Geschichten, bei denen ein bestimmter User als Autor oder Co-Autor aktiv war, sondern Du möchtest alle Geschichten, bei denen dieser bestimmte User als Autor oder Co-Autor tätig war:

a) Gib mir die Geschichten, bei denen ein bestimmter User Autor ist.
Vereinige dieses Ergebnis mit den Geschichten, bei denen dieser bestimmte Autor Co-Autor ist.
b) Zu allen diesen Geschichten benötigst Du die Favoriteneinträge (sofern vorhanden), möchtest diese zählen und bist nur an der Gesamtzahl (je Benutzer) interessiert.

a) ist eine Vereinigung (UNION) zweier Ergebnismengen:

-- Gib mir die gültigen Geschichten, bei denen User 8 Autor ist:

SELECT  
    sid  
FROM  
    Story  
WHERE  
    uid = 8  
AND  
    validated > 0  
UNION  
-- Gib mir die gültigen Geschichten, bei denen User 8 Co-Autor ist:  
SELECT  
    s.sid  
FROM  
    Story s  
INNER JOIN  
    CoAutoren c  
ON  
    s.sid = c.sid  
WHERE  
    uid = 8  
AND  
    validated > 0  

Das Ergebnis dieser UNION-Operation muss nun in Schritt b) mit den Favoriten verknüpft werden:

  
SELECT  
    g.sid,  
    COUNT(fs.item)  
FROM (  
    SELECT  
        sid  
    FROM  
        Story  
    WHERE  
        uid = 8  
    AND  
        validated > 0  
    UNION  
    SELECT  
        s.sid  
    FROM  
        Story s  
    INNER JOIN  
        CoAutoren c  
    ON  
        s.sid = c.sid  
    WHERE  
        uid = 8  
    AND  
        validated > 0) AS g  -- diese Unterabfrage benötigt zwingend  
                             -- einen Namen für den Join.  
LEFT JOIN  
    Favoriten fs  
ON  
    g.sid = fs.item AND fs.type = 'ST'  
GROUP BY  
    g.sid

liefert Dir Deine gewünschte 1 und sollte Dir auch bei komplexeren Daten die gewünschte Anzahl der Favoriteneinträge für einen bestimmten Autor/Co-Autor liefern.

Freundliche Grüße

Vinzenz