Komplizierte SQL-Verknuepfung

[Alexander Rehbein]

Hallo,

ich hab folgendes Problem:

Ich hab 3 Tabellen:

al_news
al_users
al_comments

Jetzt möchte ich die Datensätze aus al_news mit den beiden anderen verknuepfen, d.h. jeder Nnews-Datensatz hat eine user_id dessen Namen ich aus al_users hole - das klappt bis jetzt ganz gut.

Jetzt gibt es zu jedem News-Datensatz auch eine bestimmte Anzahl x Kommentare und ich möchte diese herausfinden. Das alles soll in das Query.

Ohne die Kommentare habe ich diese funktionierende Query:

SELECT al_news.*, al_users.username FROM al_news, al_users WHERE (al_news.author_id = al_users.id)

Das klappt wie gewünscht. So klug wie ich bin ;)) Habe ich mir sowas in der Theorie ueberlegt:

SELECT al_news.*, al_users.username, al_comments.COUNT(*) FROM al_news, al_users, al_comments WHERE (al_news.author_id = al_users.id AND al_news.id = al_comments.parent_id)

Aber MySQL-Front bringt mir folgenden Fehler:
You have an error in your SQL syntax near '(*) FROM al_news, al_users, al_comments WHERE (al_news.author_id = al_users.id A' at line 1

Ich wäre für eine Lösung Hilfe-Stellung sehr dankbar!

[Cheatah]

Hi,

SELECT al_news.*, al_users.username, al_comments.COUNT(*) [...]

wenn Du Gruppenfunktionen (nicht auf die gesamte Tabelle) verwenden willst, musst Du mittels GROUP BY gruppieren. Darüber hinaus ist die Selektierung von "*", also von einer nicht exakt festgelegten Spaltenmenge, außerhalb von Test- und Entwicklungszwecken sinnfrei. Gib genau diejenigen Spalten an, die Du auch brauchst - nicht mehr und auch nicht weniger.

Cheatah

[Alexander Rehbein]

Hi,

SELECT al_news.*, al_users.username, al_comments.COUNT(*) [...]

wenn Du Gruppenfunktionen (nicht auf die gesamte Tabelle) verwenden willst, musst Du mittels GROUP BY gruppieren. Darüber hinaus ist die Selektierung von "*", also von einer nicht exakt festgelegten Spaltenmenge, außerhalb von Test- und Entwicklungszwecken sinnfrei. Gib genau diejenigen Spalten an, die Du auch brauchst - nicht mehr und auch nicht weniger.

Danke erstmal für den Tipp - ich habs mal versucht:

SELECT al_news.*, al_users.username, COUNT(*) AS num_comments FROM al_news, al_users, al_comments WHERE (al_news.author_id = al_users.id AND al_comments.parent_id = al_news.id) GROUP BY al_comments.id

Aber ich erhalte jetzt zwar eine zusätzliche Spalte, aber keine Records mehr :( Ich weiss einfach nicht wie ich das anstellen soll..
(BTW. von der News-Table brauch ich alle Daten also * und COUNT geht irgendwie nur richtig mit *).

Weiss jemand noch ne Lösung?

[Cheatah]

Hi,

SELECT al_news.*, al_users.username, COUNT(*) [...] GROUP BY al_comments.id

SELECT spalte_1, spalte_2, ..., spalte_n, gruppenfunktion_1, gruppenfunktion_2, ..., gruppenfunktion_m FROM ... GROUP BY spalte_1, spalte_2, ..., spalte_n

Alles andere ergibt keinen Sinn.

Ebenso wie das Selektieren aller Spalten einer Tabelle durch "*", wie ich noch einmal erwähnen möchte. Spätestens wenn Du gruppierst musst Du _exakt_ wissen, was Du holen möchtest. Ich verurteile MySQL dafür, dass es in einer solchen Situation nicht grundsätzlich einen Fehler auswirft.

(BTW. von der News-Table brauch ich alle Daten also *

Nein. Du brauchst alle Daten, also "spalte_1, spalte_2, ..., spalte_n". Du brauchst _nicht_ "*".

und COUNT geht irgendwie nur richtig mit *).

COUNT(spaltenliste) ist eine Gruppenfunktion, keine Selektion verschiedener Spalten.

Cheatah

[Alexander Rehbein]

Hab jetzt schon alles mögliche ausprobiert, aber krieg das einfach nicht hin.

In der Hoffnung, dass mir jemand helfen kann, poste ich hier mal die DB-Struktur:

CREATE TABLE al_comments (
  id int(10) unsigned NOT NULL auto_increment,
  type tinytext NOT NULL,
  parent_id int(10) unsigned NOT NULL default '0',
  author mediumint(8) unsigned NOT NULL default '0',
  comment text NOT NULL,
  timestamp bigint(20) NOT NULL default '0',
  PRIMARY KEY  (id),
  UNIQUE KEY id (id),
  KEY id_2 (id)
) TYPE=MyISAM;

CREATE TABLE al_news (
  id smallint(5) unsigned NOT NULL auto_increment,
  author_id tinyint(4) NOT NULL default '0',
  headline tinytext NOT NULL,
  content text NOT NULL,
  timestamp bigint(20) unsigned NOT NULL default '0',
  PRIMARY KEY  (id),
  UNIQUE KEY id (id),
  KEY id_2 (id)
) TYPE=MyISAM;

CREATE TABLE al_users (
  id mediumint(8) unsigned NOT NULL auto_increment,
  level tinyint(3) unsigned NOT NULL default '254',
  username tinytext NOT NULL,
  password text NOT NULL,
  email tinytext NOT NULL,
  skin tinyint(3) unsigned NOT NULL default '0',
  lastvisit bigint(20) unsigned NOT NULL default '0',
  verylastvisit bigint(20) unsigned NOT NULL default '0',
  newsletter tinyint(3) unsigned NOT NULL default '0',
  voted text NOT NULL,
  lastadd bigint(20) unsigned NOT NULL default '0',
  PRIMARY KEY  (id),
  UNIQUE KEY id (id),
  KEY id_2 (id)
) TYPE=MyISAM;

Und jetzt das was ich will:

-Die News sollen abgerufen werden.
-In der News ist eine Spalte author_id, die wird verknüpft mit al_users -> ich will, dass statt author_id der Name aus al_users (username) angezeigt wird.
-Als letztes stehen in al_comments noch alle Kommentare die zu dieser News geschrieben wurde. Jeder Kommentar wird per parent_id einer News zugeordnet. Nun will ich die Gesamt-Anzahl an Kommentaren zu einer bestimmten News noch angezeigt bekommen.

Also im Endeffekt brauch ich folgende Spalten:
-Alle aus al_news (auch die ID)
-Username (und zur Verknüpfung id) aus al_users
-COUNT() bzw. die Anzahl der Reihen mit der gleichen parent_id, passend zur news-ID.

Ich hoffe das war verständlich.

Vielen Dank für Eure Hilfe im Voraus! Auch für Deine bisherige Cheetah!

[Klaus Mock]

Hallo,

Hab jetzt schon alles mögliche ausprobiert, aber krieg das einfach nicht hin.

Probieren ist in den meisten Fällen der falsche Weg. Wichtig ist, zu wissen, was Du von einem DMBS erwarten kannst, und wie Du es formulierst, daß es auch das gewünschet Ergebnis liefert.

Wie Cheatah schon erwähnte, gibnt es einen gravierende Bug in der SQL-Implementierung von mySQL.
"select bla,  blub, count(bling) from tabelle" sollte eigentlcih einen Fehler liefern, tut es aber nicht. Das ist definitiv eine falsche Implementierung.

Wenn Du gruppierst, _mußt_ Du unbedingt in der Liste der auszugebenden Felder (also die Feldnamen zwischen 'select' und 'from') folgendes einhalten:

-- jedes auszugebende Feld muß ausdrücklich in der Liste angeführt werden.
   * ist hier nicht mehr zulässig.
-- Alle Felder, nach denen Du nicht gruppierst, müssen mittels geeigneter
   Aggregatfunktion (COUNT, SUM, MIN, MAX...) ausgegeben werden.
-- Für alle Felder, nach denen Du gruppierst, darfst Du keine Aggregatfunktion
   verwenden.

Einfaches Beispiel :

select     spalte_1, spalte_2, count(spalte_3) from Tabelle
  group by spalte_1, spalte_2

(hier habe ich die Formatierung so angepasst, daß die beiden Spaltenlisten untereinander stehen)

Beispiel mit Join:

select  tab1.spalte_1, tab1.spalte_2, tab2.spalte_3, SUM(tab3.spalte_4)
 from tab1
  join tab2 on tab1.pk = tab2.fk
  join tab3 on tab2.pk = tab3.fk
 where tab1.wasauchimmer = 4711
 group by tab1.spalte_1, tab1.spalte_2, tab2.spalte_3

Diese Beispiele und die Beachtung der obigen Regeln sollten Dir eigentlich helfen, Dein Problem erfolgreich zu lösen.

Grüße
  Klaus

[Cheatah]

Hi,

-- Für alle Felder, nach denen Du gruppierst, darfst Du keine Aggregatfunktion
   verwenden.

was spricht gegen "SELECT id, max(id) FROM tabelle GROUP BY id"? Abgesehen davon, dass es i.d.R. verhältnismäßig sinnfrei ist, versteht sich.

Diese Beispiele und die Beachtung der obigen Regeln sollten Dir eigentlich helfen, Dein Problem erfolgreich zu lösen.

Japp, sollten sie.

Cheatah

[Alexander Rehbein]

Hiho,

also ich hab mir mal Eure Tips zu Herzen genommen und folgendes, recht kompliziert anmutendes ;), Query gestrickt:

SELECT n.id, n.headline, n.content, n.author_id, n.timestamp, u.username, count(c.parent_id) AS num_comments FROM al_news n, al_users u, al_news n2 LEFT JOIN al_comments c ON n.id = c.parent_id WHERE (n.author_id = u.id) GROUP BY c.parent_id ORDER BY timestamp DESC LIMIT 2

An sich funktioniert Alles, bis auf die Tatsache dass num_comments (also die Anzahl der Kommentare) nicht korrekt ist, sie ist immer (Tatsächliche Kommentare * Anzahl der News). Wieso ist das so?!

Ich wäre Euch sehr dankbar, wenn Ihr mir vielleicht einen konkreten Lösungsvorschlag geben könntet. Ich hab leider nicht sehr viel Zeit um alles nachzulesen (natürlich versuche ich das auch weitesgehend...)

[Cheatah]

Hi,

An sich funktioniert Alles, bis auf die Tatsache dass num_comments (also die Anzahl der Kommentare) nicht korrekt ist, sie ist immer (Tatsächliche Kommentare * Anzahl der News). Wieso ist das so?!

wenn Du $ANZAHL erwartest, aber $ANZAHL*$DATENSAETZE erhälst, dann klingt das immer sofort nach einer fehlenden Joinbedingung. Zeichne Dir die verwendeten Tabellen auf (die doppelt verwendeten auch doppelt), zeichne Linien für die Joinbedingungen dazwischen, und schau nach, was fehlt.

Wenn dann noch Unsicherheiten bleiben: Nimm die Gruppenfunktion(en) und GROUP BY Klausel weg, lass Dir die Ergebnisse ausgeben; eventuell mit zusätzlichen Spalten.

Cheatah

[Nogga]

Hi,

also ich raste bald aus, ich krieg das einfach nicht hin! Ich hab hier mal ne kleine Grafik gebastelt, in der Hoffnung jemand kann mir die Lösung sagen - ich komm einfach nicht mehr weiter!

+----+-----------+----------+---------+-----------+       +--------------+
tbl_news    | id | author_id | headline | content | timestamp |   +   | num_comments |
            +----+-----------+----------+---------+-----------+       +--------------+
                   ^                                                         ^
                   |                                                         |
                   |                                          +--------------+
                   |                                          |
                   | username                                 | count(parent_id)
                   |                                          |
                   |                                          |
                 +----+----------+                      +-----------+
tbl_users        | id | username |       tbl_comments   | parent_id |
                 +----+----------+                      +-----------+

Wie schon gesagt, ich will die Anzahl der Kommentare (jeder hat eine parent_id, die zu einer News gehört) in num_comments stehen haben. Ein Query hätte ich da schon: Aber es liefert mir sehr seltsame Zahlen für num_comments - ich hab schon alles ausprobiert, aber ich schaffs einfach nicht :(

SELECT n.id, n.headline, n.content, n.author_id, n.timestamp, u.username, count(c.parent_id) AS num_comments
FROM al_news n, al_users u, al_news n2 LEFT JOIN al_comments c ON n2.id = c.parent_id
WHERE (n.author_id = u.id) GROUP BY c.parent_id

[Klaus Mock]

Hallo,

Wie schon gesagt, ich will die Anzahl der Kommentare (jeder hat eine parent_id, die zu einer News gehört) in num_comments stehen haben. Ein Query hätte ich da schon: Aber es liefert mir sehr seltsame Zahlen für num_comments -

Du hast anscheinend noch immer nicht die Geschichte mit den Joins verstanden

ich hab schon alles ausprobiert, aber ich schaffs einfach nicht :(

.. nur die richtige VAriante noch nicht;-)

SELECT n.id, n.headline, n.content, n.author_id, n.timestamp, u.username, count(c.parent_id) AS num_comments

bis hier her stimmt's ja schon einmal.

FROM al_news n, al_users u, al_news n2

wozu diese Liste von Tabellen?
und wozu brauchst Du n2?

Dir ist scheinbar nicht bewußt das diese Liste Kreuzprodukte erzeugt,  d.h. die Ergebnismenge enthält (ohne weitere Maßnahmen) eine kombination aus allen beteiligten Tabellen, wobei die Anzahl der Datensätze aus diesen Tabellen mit einander multipliziert werden. bei drei Tabellen mit je 100 Datensätzen ergibt das eine Ergebnismenge mit 1 Million Datensätzen.

LEFT JOIN al_comments c ON n2.id = c.parent_id

Gut im Ansatz nur nicht fertiggemacht.

WHERE (n.author_id = u.id)

Auch eine Möglichkeit einen Join zu realisieren, sollte aber nur gemacht werden, wenn die Dtaenbank keine echten Joins kann.

GROUP BY c.parent_id

Ups, da hast Du die vollkommen falsche spalte erwischt;-)
Lies nochmals sorgfältig meine Ausführungen in [pref:t=40339&m=221252] durch.

Probier mal:

SELECT n.id, n.headline, n.content, n.author_id,
       n.timestamp, u.username, count(c.parent_id) AS num_comments
   FROM al_news n
   JOIN al_users u    ON n.author_id = u.id
   JOIN al_comments c ON n.id = c.parent_id
   GROUP BY n.id, n.headline, n.content, n.author_id, n.timestamp, u.username

Grüße
  Klaus