Getting this query to work?

Question

Heya!, I have the below query:

SELECT t1.pm_id 
  FROM fb_user_pms AS t1, 
       fb_user_pm_replies AS t2 
 WHERE (t1.pm_id = '{$pm_id}' 
   AND t1.profile_author = '{$username}' 
    OR t1.pm_author = '{$username}' 
   AND t1.pm_id = t2.pm_id 
   AND t2.pm_author = '{$username}' 
   AND COUNT(t2.reply_id) > 0) 
   AND t1.deleted = 0

However, I'm getting a grouping error - my guess is its caused by the AND COUNT(t2.reply_id) > 0?

How can I rectify the above query to make it work.

Hope someone can help.

Cheers!

Answer 1

• The condition with COUNT must be in the HAVING part. It can not be part of the WHERE part.
• The SELECT part must be also use aggregate functions for example MAX(t1.pm_id)

Answer 2

The aggregate function COUNT can't go in the WHERE clause. You should use a GROUP BY and put it in the HAVING clause.

SELECT t1.pm_id
FROM fb_user_pms AS t1
JOIN fb_user_pm_replies AS t2 ON t1.pm_id = t2.pm_id
WHERE (
         (t1.pm_id = '{$pm_id}' AND t1.profile_author = '{$username}') OR
         (t1.pm_author = '{$username}' AND t2.pm_author = '{$username}')
      ) AND t1.deleted = 0
GROUP BY t1.pm_id
HAVING COUNT(t2.reply_id) > 0

If t2.reply_id is a NOT NULL column then you don't need the HAVING clause at all.

Answer 3

The error is because you can't use an aggregate function (COUNT, MIN, MAX, AVG, etc) in the WHERE clause, without it being inside a subquery. Only the HAVING clause allows you to use aggregates without being wrapped in subqueries.

But checking for replies to be more than zero is not necessary on an INNER JOIN - that guarantees that there will be at least one reply associated to the fb_user_pms record. The JOIN also means that the information in t1 will be duplicated for every supported record in fb_user_pm_replies. IE: If a fb_user_pms record has three fb_user_pm_replies records related to it, you'll see the fb_user_pms record in the result set three times.

The query you want to use is:

SELECT t1.pm_id 
  FROM fb_user_pms AS t1 
 WHERE t1.pm_id = '{$pm_id}' 
   AND '{$username}' IN (t1.profile_author, t1.pm_author)
   AND t1.deleted = 0
   AND EXISTS(SELECT NULL
                FROM fb_user_pm_replies AS t2 
               WHERE t2.pm_id = t1.pm_id
                 AND t2.pm_author = '{$username}')

The EXISTS clause returns true or false, based on the WHERE criteria. It also won't duplicate t1 results.