Alle Foren abfragen mit Themen- und Beitragsanzahl

[sjBlack]

Hallo, bin hier neu und komme direkt mit einem
verflixten sql Problem abgekrochen.

Ich versuche alle Foren, die unter dem Forum der id 1 liegen
abzurufen mit samt Anzahl der Themen, die sich innerhalb
dieses Forums befinde und der Anzahl der Beiträgen je Thema.

SELECT sj_forums.title, sj_forums.desc, sj_topics.COUNT(*) AS c_topics, sj_posts.COUNT(*) AS c_posts
FROM sj_forums, sj_topics, sj_posts
WHERE sj_forums.sub=0 AND sj_topics.sub=sj_forums.id AND sj_posts.sub=sj_topics.id
ORDER BY sj_forums.pos ASC

Das klappt schonmal nicht...

Fehler

SQL-Befehl: Dokumentation

SELECT sj_forums.title, sj_forums.desc, sj_topics.COUNT(
*
) AS c_topics, sj_posts.COUNT(
*
) AS c_posts
FROM sj_forums, sj_topics, sj_posts
WHERE sj_forums.sub =0
AND sj_topics.sub = sj_forums.id
AND sj_posts.sub = sj_topics.id
ORDER BY sj_forums.pos ASC
LIMIT 0 , 30

MySQL meldet: Dokumentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '* ) AS c_topics , sj_posts.COUNT ( * ) AS c_posts  FROM sj_forums , sj_topics , ' at line 1

Die sub-Spalte bei sj_forums gibt an, unter welchem anderen
Forum dieses liegt. Die sub-Spalte in der sj_topics-Tabelle gibt
an, in welchem Forum das Thema liegt. Und die sub-Spalte der
sj_posts-Tabelle gibt an, in welchem thema sich der Beitrag
befindet. Ich arbeite hier nach dem Vaterzeiger-Prinzip.
Ich glaube nicht, dass Nested Sets hier angebracht währen
geschweige denn beherrsche das Prinzip.

Wie bekomme ich so eine Abfrage jetzt hin?
Help!

[sjBlack]

Keiner nen Plan?

[Markus Kinzler]

Sieht eigentlich ok aus versuch es mal mit expliziten Joins

[sjBlack]

Sieht eigentlich ok aus versuch es mal mit expliziten Joins

SELECT sj_forums.title, sj_forums.desc, sj_topics.COUNT(*) AS c_topics, sj_posts.COUNT(*) AS c_posts
FROM sj_forums JOIN sj_topics JOIN sj_posts
WHERE sj_forums.sub=0 AND sj_topics.sub=sj_forums.id AND sj_posts.sub=sj_topics.id
ORDER BY sj_forums.pos ASC

Fehler

SQL-Befehl: Dokumentation

SELECT sj_forums.title, sj_forums.desc, sj_topics.COUNT(
*
) AS c_topics, sj_posts.COUNT(
*
) AS c_posts
FROM sj_forums
JOIN sj_topics
JOIN sj_posts
WHERE sj_forums.sub =0
AND sj_topics.sub = sj_forums.id
AND sj_posts.sub = sj_topics.id
ORDER BY sj_forums.pos ASC
LIMIT 0 , 30

MySQL meldet: Dokumentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '* ) AS c_topics , sj_posts.COUNT ( * ) AS c_posts FROM sj_forums JOIN sj_topics' at line 1

[Markus Kinzler]

[highlight="SQL"]
SELECT
f.title, f.desc, t.COUNT(*) AS c_topics, p.COUNT(*) AS c_posts
FROM
sj_forums f
JOIN sj_topics t on tsub=f.id
JOIN sj_posts p on p.sub = t.id
ORDER BY
f.pos ASC;
[/highlight]

[sjBlack]

Fehler

SQL-Befehl: Dokumentation

SELECT f.title, f.description, t.COUNT(
*
) AS c_topics, p.COUNT(
*
) AS c_posts
FROM sj_forums f
JOIN sj_topics t ON tsub = f.id
JOIN sj_posts p ON p.sub = t.id
ORDER BY f.pos ASC
LIMIT 0 , 30

MySQL meldet: Dokumentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '* ) AS c_topics , p.COUNT ( * ) AS c_posts FROM sj_forums f JOIN sj_topics t ON' at line 1



-______________________-""

habe jetzt auch die spalte desc auf description umbenannt und versucht mit

SELECT f.title, f.description, t.COUNT(*) AS c_topics, p.COUNT(*) AS c_posts
FROM sj_forums f JOIN sj_topics t ON tsub=f.id JOIN sj_posts p ON p.sub=t.id
ORDER BY f.pos ASC

Eien höhere Macht will Schleifen in meinem source, die pro
Durchlauf eine Datenbankanfrage sendet...

[Markus Kinzler]

Ersetze mal t.COUNT(*) mit COUNT(t.*)

[sjBlack]

SELECT f.title, f.description, COUNT(t.*) AS c_topics, p.COUNT(*) AS c_posts
FROM sj_forums f JOIN sj_topics t ON tsub=f.id JOIN sj_posts p ON p.sub=t.id
ORDER BY f.pos ASC

Fehler

SQL-Befehl: Dokumentation

SELECT f.title, f.description, COUNT( t. * ) AS c_topics, p.COUNT(
*
) AS c_posts
FROM sj_forums f
JOIN sj_topics t ON tsub = f.id
JOIN sj_posts p ON p.sub = t.id
ORDER BY f.pos ASC
LIMIT 0 , 30

MySQL meldet: Dokumentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '* ) AS c_topics , p.COUNT ( * ) AS c_posts FROM sj_forums f JOIN sj_topics t ON' at line 1


Auch wenn ich p.COUNT(*) in COUNT(p.*) mitersetze.

// edit
SELECT f.title, f.description, COUNT(t.*) AS c_topics, COUNT(p.*) AS c_posts
FROM sj_forums f JOIN sj_topics t ON t>>>.<<<sub=f.id JOIN sj_posts p ON p.sub=t.id
ORDER BY f.pos ASC

Habe hier grad mal einen fehlenden Punkt eingegeben. Immernoch nichts...

[Markus Kinzler]

Nimm statt * ID

[sjBlack]

ohmann...

SQL-Befehl: Dokumentation

SELECT f.title, f.description, COUNT( t.id ) AS c_topics, COUNT( p.id ) AS c_posts
FROM sj_forums f
JOIN sj_topics t ON t.sub = f.id
JOIN sj_posts p ON p.sub = t.id
ORDER BY f.pos ASC
LIMIT 0 , 30

MySQL meldet: Dokumentation
#1140 - Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

[Markus Kinzler]

Frisst das MySQL?
[highlight="SQL"]
SELECT
f.title, f.description,
(select COUNT( id ) from sj_topics where id = f.sub AS c_topics,
( select COUNT( id ) from sj_posts where id in (select sub from sj_topics where id = f.sub)) AS c_posts
FROM
sj_forums f
ORDER BY
f.pos ASC
LIMIT 0 , 30
[/highlight]

[sjBlack]

Wie erwartet -> nein

[Markus Kinzler]

Was meckert er an außer der Klammer welche ist vergessen habe?

[sjBlack]

MySQL meldet: Dokumentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS c_topics,
( SELECT COUNT( id ) FROM sj_posts WHERE id IN (SELECT sub FROM sj' at line 2


Mir ist dein letzter Query Latein...

[Markus Kinzler]

Hast du die fehlende Klammer ergänzt?