query problem

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
itsmani1
Forum Regular
Posts: 791
Joined: Mon Sep 29, 2003 2:26 am
Location: Islamabad Pakistan
Contact:

query problem

Post 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
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: query problem

Post 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]
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply