mysql join newbie Problem

[Gerald]

Hi,

ich bin nur Hobbyprogrammierer und trotz verschiedenen Tutorials verstehe ich viele in MySQL noch immer nicht. Ich habe ein (denke ich) recht einfaches Problem zu lösen, wäre nett, wenn mir einer auf die Sprünge hilft.

Es geht darum, die Anzahl Kommentare zu einem Posting zu ermitteln.

Tabelle "posts"

ID     TITEL     [..]
1      Hallo
2      Super
3      Test...

Tabelle "comments"

ID     POST_ID     TEXT
1      1           Antwort zu post 1
2      1           Antw. zu post 1
3      2           Antw. zu post 2

usw usw.
Ich denke soweit recht simpel und wie es sein sollte (?)

Nun möchte ich den Inhalt von "posts" ausgeben und gleichzeitig eine Variable generieren, die mir in der Übersicht gleich die Anzahl der Comments zeigt, also etwa:

"Teaser Text von Post XY" (7 Kommentare)

Meine bisherige SQL ist:

SELECT
 posts.*,
  count(posts.id) as comments

FROM
 posts

LEFT JOIN
 comments ON comments.post_id = posts.id

GROUP BY
 posts.id

Leider ergibt das nicht die richtigen Werte.
Ich denke da stimmt was mit meiner Denkweise nicht.
Wäre froh, wenn mir jemand hier aushelfen kann.

Danke,
Gerald

[Frank (no reg)]

Hi,

vielleicht solltest du besser count(comments.post_id) zählen statt count(posts.id)?

Sag mir doch mal warum.

Grüsse,
Frank

[Gerald]

Hi,

vielleicht solltest du besser count(comments.post_id) zählen statt count(posts.id)?

Sag mir doch mal warum.

Weia, die Lösung war so nahe. Natürlich, da lag mein Fehler. Nun funktioniert es, danke Frank.

Gerald

[Rouven]

Hello,

SELECT
posts.*,
GROUP BY
posts.id

du hast zwar mittlerweile eine funktionierende Lösung gefunden, aber ich möchte dich dennoch darauf hinweisen, dass du eine hohe Toleranz von MySQL ausnutzt. Der SQL Standard sieht eigentlich vor, dass man innerhalb einer Gruppierung nur zwei Arten von Spalten abfragen darf:

1. solche, nach denen gruppiert wurde (hier: posts.id)
2. solche, die durch eine Aggregation oder vergleichbare Funktion entstanden sind (COUNT, MAX, AVG, ...)

Deine posts.* (davon mal abgesehen, dass * nur zu Testzwecken verwendet werden sollte) ergibt aber mehr Spalten - MySQL ist das einzige mir bekannte DBMS, dass dir dies durchgehen lässt. Die Frage ist nämlich die: Welches konkrete Post soll ich denn ausliefern? In deinem Fall magst du argumentieren "moment, ich gruppiere doch nach dem Primärschlüssel, da kommt ja nur ein einziges raus" - sauber ist es trotzdem nicht. Aber alleine aus diesem Argument lässt sich auch die saubere Schreibweise dieser Abfrage ableiten:

  
SELECT  
  posts.id, posts.titel, ...  
  count(comments.id) as comments  
 FROM  
 `posts`  
 LEFT JOIN  
 `comments` ON comments.post_id = posts.id  
 GROUP BY  
  posts.id, posts.titel, ...  

MfG
Rouven