I have a need to get record counts from a sub-table and concatenated values from another group of sub-tables. The concatenation problem was handled in a different thread. The problem now is separating the counts from the concatenations. What is happening is I am getting the counts multiplied by the number of concatenated values returned by the group by statement.
The table structure is as follows
forums as t1 - forumid, userid
forumsubcats as t2 - forumid, subcatid
subcats as t3 - subcatid, categoryid, subcatname
categories as cat - categoryid, catname
then the other table that I need to get the count from is called
posts as p - postid, forumid
and then there is a single join to get a username from a userid
users as u - userid, username
The query currently looks like this
SELECT t1.forumid,
GROUP_CONCAT(distinct cat.catname, '-', subcatname) AS categories,
COUNT( p.forumid ) AS counter,
username
FROM forums as t1
INNER JOIN forumsubcats as t2
ON t2.forumid = t1.forumid
INNER JOIN subcats as t3
ON t2.subcatid = t3.subcatid
INNER JOIN categories AS cat
ON cat.categoryid = t3.categoryid
INNER JOIN posts as p
ON p.forumid = t1.forumid
INNER JOIN users as u
ON u.userid = t1.userid
GROUP BY t1.forumid
The concatenations work great, but if there are 3 values concatenated, the count is multiplied by 3. Also, if I strip the concatenations out of the qurey, the counts work fine. Any help as to how to separate the concatenations from the count would be greatly appreciated. Thanks