Page 1 of 1

query problem

Posted: Fri Jul 18, 2008 2:42 am
by itsmani1
I have 3 tables

category, topics, replies

I want to count topics and replies posted in a category

I can get topics count but having problem with replies

here is my query

Code: Select all

SELECT 
  zzf_category.PK_ID,
  count(zzf_topics.PK_ID) AS topics,
  zzf_category.catname     
FROM
  zzf_category
  LEFT OUTER JOIN zzf_topics ON (zzf_category.PK_ID = zzf_topics.FK_CATEGORY_ID)
WHERE
  zzf_category.catpar = catid
GROUP BY
  zzf_category.PK_ID 
order by             zzf_category.PK_ID asc

Re: query problem

Posted: Fri Jul 18, 2008 2:55 am
by VladSun
Untested
[sql]SELECT    zzf_category.PK_ID,    count(DISTINCT zzf_topics.PK_ID) AS topics,    count(zzf_replies.PK_ID) AS replies,    zzf_category.catname    FROM    zzf_repliesINNER JOIN     zzf_topics ON zzf_topics.PK_ID = zzf_replies.FK_TOPICS_IDINNER JOIN     zzf_category ON zzf_category.PK_ID = zzf_topics.FK_CATEGORY_IDWHERE    zzf_category.catpar = catidGROUP BY    zzf_category.PK_IDORDER BY                 zzf_category.PK_ID ASC[/sql]