MySQL: Count über zwei Tables

[Harald]

Hi,

folgendes Szenario:

Angenommen ich habe zwei Tabellen für ein Forum, eine für Threads, die andere für Posts.
Nun steht in beiden Tabellen in der Spalte Autor der Autorname, bzw. dessen ID.
Ich möchte jetzt als Statistik auswerten, welcher Autor wie viele Beiträge geschrieben hat, wobei natürlich Threads+Posts gezählt werden.
Bei einer Tabelle ist das ganze kein Problem:

SELECT COUNT(autor) AS counts, autor FROM threads GROUP BY autor ORDER BY counts DESC

Nur wie müsste das Ganze aussehen, wenn ich außerdem noch die Posts des Autors mitzählen will?

(Sicher könnte ich auch alle Autoren einzeln abfragen oder das hinterher zusammenzählen, allerdings muss das ja auch so gehen)

MfG Harald

[Axel Richter]

Hallo,

folgendes Szenario:

Angenommen ich habe zwei Tabellen für ein Forum, eine für Threads, die andere für Posts.
Nun steht in beiden Tabellen in der Spalte Autor der Autorname,

^In _beiden_ Tabellen steht der Autorname? Das wäre schlecht.

bzw. dessen ID.

^Das wäre gut. Wie ist es denn nun?

Ich möchte jetzt als Statistik auswerten, welcher Autor wie viele Beiträge geschrieben hat, wobei natürlich Threads+Posts gezählt werden.
Bei einer Tabelle ist das ganze kein Problem:

SELECT COUNT(autor) AS counts, autor FROM threads GROUP BY autor ORDER BY counts DESC

Nur wie müsste das Ganze aussehen, wenn ich außerdem noch die Posts des Autors mitzählen will?

Du musst die beiden Tabellen verbinden. SQL benutzt dazu JOINs. Für genauere Angaben müsste man Deine Tabellenstrukturen genauer kennen.

viele Grüße

Axel

[Harald]

bzw. dessen ID.
               ^Das wäre gut. Wie ist es denn nun?

Ja, die ID steht drin.

Du musst die beiden Tabellen verbinden. SQL benutzt dazu JOINs.

In diese Richtung hab ich auch schon einige Versuche unternommen, habe allerdings kein vernüftiges Ergebnis erzielt.

Für genauere Angaben müsste man Deine Tabellenstrukturen genauer kennen.

Also (ein wenig gekürzt, weil nicht alles relevant ist):

Tabelle:

Threads:
      id
      autor
      article
      etc.

Posts:
      id
      thread_id
      autor
      article
      etc.

Wie gesagt möchte ich dann halt einfach als Statistik ausgeben, wie viele Einträge der Autor hat.
Beispiel:

Inhalt der Tabelle Threads:

id=1      autor=1
id=2      autor=1
id=3      autor=3
id=4      autor=2

Inhalt der Tabelle Posts:

id=1      autor=2
id=2      autor=2
id=3      autor=1
id=4      autor=2

Das heißt ich möchte im Endeffekt als Ergebnis haben, dass:

Autor 1 = 3 Beiträge
Autor 2 = 4 Beiträge
Autor 3 = 1 Beitrag

[Axel Richter]

Hallo,

Du musst die beiden Tabellen verbinden. SQL benutzt dazu JOINs.
In diese Richtung hab ich auch schon einige Versuche unternommen, habe allerdings kein vernüftiges Ergebnis erzielt.

Ja, jetzt wo Du es genauer beschreibst, sehe ich es auch ;-)). Das geht nicht mit JOINs. Hier müssen zunächst die Datensätze beider Tabellen in eine Datensatzmenge kommen (UNION). Diese Datensatzenge muss dann nach Autor gruppiert werden.

Tabelle:

Threads:
      id
      autor
      article
      etc.

Posts:
      id
      thread_id
      autor
      article
      etc.

Wie gesagt möchte ich dann halt einfach als Statistik ausgeben, wie viele Einträge der Autor hat.

SELECT autor, Count(*) AS Anzahl
FROM (SELECT autor FROM Threads UNION ALL SELECT autor FROM Posts) AS ThreadsPosts
GROUP BY autor;

Dafür muss Dein MySQL Subqueries unterstützen (Starting with version 4.1, MySQL). Sonst brauchst Du wirklich zwei Queries, deren Count Du dann mit einer Programmiersprache zusammenzählst.

viele Grüße

Axel

[noboy]

das ist leider nicht richtig. join ist hier das richtige.

union hat einen anderen zweck (mal in doku schauen)

[Axel Richter]

Hallo,

das ist leider nicht richtig. join ist hier das richtige.

Ach? Dann zeig mir bitte den JOIN, der folgendes kann:

Tabelle:
   Threads:
      id
      autor
      article
      etc.
   Posts:
      id
      thread_id
      autor
      article
      etc.
Wie gesagt möchte ich dann halt einfach als Statistik ausgeben, wie viele Einträge der Autor hat.

Er will die Anzahl der Datensätze in den Tabellen "Threads" _und_ "Posts" ermitteln, die vom selben Autor (Feld: "autor") stammen.

union hat einen anderen zweck (mal in doku schauen)

Nein.

Wie gesagt, zeige mir den JOIN. Ich lerne gerne was dazu.

viele Grüße

Axel

[nobody]

offensichtlich meinst du den fall, daß es keine direkte 1:n beziehung gibt. (master:detail über mehrere keys)

mit left/right join wird die eine tabelle gemäß der where bedingung komplett gelesen, während die andere gemäß der on bedingung verknüpft wird, wobei fehlende verknüpfungen mit null ergänzt werden.

also:

select count(t1.autor) as t1autor, count(t2.autor) as t2autor, t1.autor
from t1 left join t2 on t1.autor = t2.autor and t1.artikel = t2.artikel
group by t1.autor

ergibt ein 3 spaltiges resultset mit je einer zeile pro autor, wobei in den beiden anderen spalten die anzahl der sätze aus der 1. und 2. tabelle steht.

[Axel Richter]

Hallo,

offensichtlich meinst du den fall, daß es keine direkte 1:n beziehung gibt. (master:detail über mehrere keys)

Nein. Ich meine den Fall, der in [pref:t=76241&m=439108] beschrieben wurde.

mit left/right join wird die eine tabelle gemäß der where bedingung komplett gelesen, während die andere gemäß der on bedingung verknüpft wird, wobei fehlende verknüpfungen mit null ergänzt werden.

Nein. Es wird eine Datensatzmenge mit Feldern beider Tabellen gebildet, in der alle Datensätze der einen Tabelle enthalten sind. Felder aus der anderen Tabelle werden dann und so oft mit den entsprechenden Inhalten in die Datensatzmenge einverknüpft, wie die Datensätze dieser Felder der ON-Klausel entsprechen. Ist das nicht der Fall, ist der Wert dieser Felder in der Datensatzmenge NULL.  Diese so entstandene Datensatzmenge wird dann entsprechend der WHERE-Klausel gefiltert.

also:

select count(t1.autor) as t1autor, count(t2.autor) as t2autor, t1.autor
from t1 left join t2 on t1.autor = t2.autor and t1.artikel = t2.artikel
group by t1.autor

ergibt ein 3 spaltiges resultset mit je einer zeile pro autor, wobei in den beiden anderen spalten die anzahl der sätze aus der 1. und 2. tabelle steht.

Nein.
Das angegebene Beispiel ist:

Inhalt der Tabelle Threads:
id=1      autor=1
id=2      autor=1
id=3      autor=3
id=4      autor=2

Inhalt der Tabelle Posts:
id=1      autor=2
id=2      autor=2
id=3      autor=1
id=4      autor=2

Dein

SELECT Threads.autor AS Threads_autor, Posts.autor AS Posts_autor
FROM Threads LEFT JOIN Posts ON Threads.autor = Posts.autor;

ergibt:
Threads_autor   Posts_autor
1               1
1               1
3
2               2
2               2
2               2

Lässt man das nun zählen

SELECT Threads.autor, Count(Threads.autor) AS Threads_autor, Count(Posts.autor) AS Posts_autor
FROM Threads LEFT JOIN Posts ON Threads.autor = Posts.autor
GROUP BY Threads.autor;

ergibt das:
autor  Threads_autor  Posts_autor
1      2              2
2      3              3
3      1              0

Meine Lösung

SELECT autor, Count(*) AS Anzahl
FROM (SELECT autor FROM Threads UNION ALL SELECT autor FROM Posts) AS ThreadsPosts
GROUP BY autor;

ergibt:
autor   Anzahl
1       3
2       4
3       1

Der Ausgangsposter wollte in [pref:t=76241&m=439108]:

Autor 1 = 3 Beiträge
Autor 2 = 4 Beiträge
Autor 3 = 1 Beitrag

viele Grüße

Axel

[wahsaga]

hi,

Threads:
      id
      autor
      article
      etc.

Posts:
      id
      thread_id
      autor
      article
      etc.

wodurch rechtfertigen sich in deinen augen _zwei_ tabellen für diese daten, die im grossen und ganzen offenbar die gleiche struktur haben?

warum nimmst du nicht eine tabelle, in die du beide datenarten reinnimmst?

bzw: wo genau ist für dich der unterschied zwischen einem thread und einem posting? für mich ist das irgendwie das gleiche - und das ein gewisses posting einen thread startet - während andere postings folgepostings von anderen postings sind.

wenn du also zu jedem posting noch mit aufnimmst, auf welches posting es folgt - und dann bei threads eröffnenden postings dieses attribut einfach leer lässt - kommst du m.E. gut mit einer tabelle aus.

gruss,
wahsaga

[Harald]

Danke auch euch beiden,
allerdings (@wahsaga) hat das Ganze schon seinen Sinn mit zwei Tabellen (sonst hätte ich das ja nicht so gemacht), weil es einige Dinge wesentlich einfach gestaltet und außerdem bei einem Thread in diesem Falle einiges mehr gespeichert werden muss als bei einem Post.
Und (@MO), natürlich steht in autor die ID drin, hatte ich bereits geschrieben.
Das Problem ist jetzt allerdings gelöst.

[nobody]

zwei tabellen sind in ordnung. nennt sich 1:n beziehung.

der autor ist die mastertabelle und die beiträge ist die detailtabelle.

[wahsaga]

hi,

zwei tabellen sind in ordnung. nennt sich 1:n beziehung.
der autor ist die mastertabelle und die beiträge ist die detailtabelle.

ich bezog mich darauf, dass er für threads und posts zwei tabellen benutzt.

das es darüber hinaus noch eine weitere für die daten das autors gibt, ist selbstverständlich.

aber ob obige zwei tabellen nicht eher eine sind, halte ich immer noch für überdenkenswert.

gruss,
wahsaga

[MO]

Alsooo...gehe mal davon aus, das du noch nicht viel mit der Normalisierung von Datenbanken zu tun hattest.
Die id in deinen Threads ist Primärschlüssel?
Davon gehe ich nun mal aus...also primärschlüssel der Threads
Genauso bei Posts.
Bei autor steht der Name drin? oder dessen id?
Name wäre ganz schlecht...damit bekommst du Redundanzen.
Normalerweise macht man das folgendermaßen:

tabelle1:
AutorID     Integer   Primärschlüssel
AutorName   Char
(Diesen kann man weiter normalisieren in Name, vorname, adresse, etc)
Nur mal der einfachheit halber

tabelle2:
BeitragID Integer   Primärschlüssel
AutorID  Integer   Fremdschlüssel
Article  Text
Article-Art (Thread or Post kann auch wiederum ausgelagert werden)

Dann der select:
Select count(*) AS Anzahl,AutorName FROM tabelle1 AS t1, tabelle2 AS t2 Where t1.AutorID=t2.AutorID GROUP BY AutorName;

Diese Abfrage so funktioniert nur bei MySQL. Wenn du eine andere Datenbank hast wird es etwas komplizierter. Dann mußt du mit On, Where und Group By arbeiten.

Hoffe, dir etwas geholfen zu haben

Gruß

MO

[nobody]

die abfrage ist völlig in ordnung un funktioniert auf jedem sql-server. die notation ist allerdings der alte sql-standard.
ich glaube ab sql92 gehts dann so:

select ..... t1 [inner|right|left|outer] join t2 on t1.key = t2.key

mache erlauben statt on ... auch using(key), wenn in beiden der gleiche.