selct with a count

[ser_g]

Hi guys! How can I count comments to my pages within a pages list query (like
replyes in a forum topic list) My DB is MySQL Here is a query I've done
select folders.ID, folders.name, folder_comments.folder_id, COUNT(folders.ID IN
(folder_comments.folder_id)) AS cnt from folders, folder_comments WHERE
folders.Parent_ID = 77 AND folders.Status = 2 GROUP BY folders.id it returns
some list with the same CNT value for each entry. Please help me! Thank you.

[eastinq]

It looks like your missing a join between the two tables.

folders.ID = folder_comments.ID

[paross1]

Since you are using MySQL, it would depend on what version you are using, since
you can not use SUB-SELECTS with MySQL versions prior to 4.1 If you have 4.1,
then something like the attached query may work, although I'm uncertain about
the relationship between folder and folder_comment. I am assuming that
folder.id = folder_comment.folder_id.

Phil



SELECT f.ID,
f.name,
fc.folder_id,
(SELECT COUNT(f1.ID)
FROM folders AS f1
WHERE f1.ID = fc.folder_id) AS cnt
FROM folders AS f,
folder_comments AS fc
WHERE f.Parent_ID = 77
AND f.Status = 2
AND f.id = fc.folder_id
GROUP BY f.id