SELECT cc.collection_id,cc.title,cc.type,cc.alias as forum_alias,SUBSTRING(cc.description,1,200) as short_desc,
(SELECT COUNT(boardmessage_id) FROM boardmessages WHERE parent_type='collection' AND collection_id=cc.collection_id AND is_active=1) as total_threads,(SELECT count(boardmessage_id) FROM boardmessages
WHERE parent_type IN('message','reply','reply_on_reply') AND collection_id=cc.collection_id AND is_active=1) as total_replies
FROM contentcollections cc WHERE cc.type=? AND cc.is_active=? ORDER BY cc.created DESC

Is this the efficient way of querying the database because total_threada,total_replies both in the same table so multiple joining same table with contentcollections table also returns the wrong result.

Any help or suggestion would be greatly appreciated.

Thanks

Hey Thanks for your reply , i am doing the same way like
SELECT cc.collection_id, cc.title, cc.type, cc.alias AS forum_alias, SUBSTRING(cc.description,1,200) AS short_desc, COALESCE(bm.total_threads,0) AS total_threads, COALESCE(bm.total_replies,0) AS total_replies,COALESCE(bm.last_updated,0) AS last_updated FROM contentcollections cc LEFT OUTER JOIN ( SELECT collection_id, COUNT(CASE WHEN parent_type = 'collection' THEN 'humpty' END ) AS total_threads, COUNT(CASE WHEN parent_type IN ('message','reply','reply_on_reply') THEN 'dumpty' END ) AS total_replies,CASE WHEN parent_type IN ('message','reply','reply_on_reply') THEN DATE_FORMAT(created ,'%b %d,%Y at %r') END AS last_updated FROM boardmessages WHERE is_active = 1 GROUP BY collection_id ORDER BY created DESC ) AS bm ON bm.collection_id = cc.collection_id WHERE cc.type=1 AND cc.is_active=1 ORDER BY cc.created DESC;

But it always return last_updated as 0.??

first of all, i can't read that without a lot of effort, and i'm lazy

i already went to considerable trouble to format my previous reply, and you just disregarded that formatting? why?

as far as your problem with the 0 date is concerned, i suggest you apply an aggegate to it