Count zählt falsch

[Hiraja]

Hallo Leute ich habe folgende Datenbank:

Tabelle Story, Favoriten und CoAutoren

Wie man unschwer erkennen kann geht es hier um eine Geschichtendatenbank mit Multiautorenfunktion und Favoriten.

Die Daten darin sind folgendermaßen aufgebaut:
Der Hauptautor legt die Geschichte an und steht dort in uid. sid ist dann der Schlüssel der Geschichte.
Andere User kann er als Co-Autoren bestimmen, welche dann über den Schlüssel sid und uid in der Tabelle CoAutoren steht.
Als nächstes kann ein anderer User eine Geschichte als Favorit speichern. Hierbei wird die sid der Geschichte als item gespeichert.

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|
---------
| 1 | 8 |
---------

CoAuthors:
---------
|sid|uid|
---------
| 1 | 1 |
---------
| 1 | 2 |
---------
| 1 | 3 |
---------
| 1 | 4 |
---------

Favoriten:
----------
|item|uid|
---------
| 1  | 7 |
----------

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

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

Der SQL soll also für uid 1,2,3,4 und 8 das selbe Ergebnis bringen und für 7 darf er nix bringen.

Ich bin echt schon wahrsinnig und weiß nicht weiter. Hoffe ihr könnt mir helfen.

MFG Hiraja

[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

[Hiraja]

Puh, das war wirklich ne MENGE Info - ich hatte das ganze auch shcon zerlegt gehabt (das count und das group) weggelassen und zig andere Dinge ausprobiert und daher hab ich schon gesehen, daß ich 4 Einträge kriege.

Die Lösung funzt - bis auf einen kleinen Fehler beim union gibt es das Feld uid in beiden Tabellen, da hat er gemeckert, aber danke danke danke

[Karin]

Hi,

das müsste schon ein COUNT(DISTINCT fs.item) sein.

LG

[Vinzenz Mai]

Hallo Karin,

das müsste schon ein COUNT(DISTINCT fs.item) sein.

nein, das liefert nicht das gewünschte Ergebnis:

Ein zweiter Benutzer (uid 5) fügt die erste Geschichte zu seinen Favoriten hinzu:

Favoriten:

item|uid
----------
  1  | 7
  1  | 5

In diesem Fall erhältst Du mit COUNT(DISTINCT fs.item) immer noch 1, obwohl das gewünschte Ergebnis nun 2 wäre.

Freundliche Grüße

Vinzenz

[Karin]

Hallo Vinzenz,

In diesem Fall erhältst Du mit COUNT(DISTINCT fs.item) immer noch 1, obwohl das gewünschte Ergebnis nun 2 wäre.

richtig, wenn, dann müsste man DISTINCT fs.uid zählen, wobei das dann natürlich pro Geschichte zählt, nicht über alle Geschichten für einen Autor. Was da genau gewünscht ist, ist mir aber auch nicht ganz klar.

LG