Naz,
That query logic runs without a error on the server I have to hand
(5.0.37), but it has three issues:
(i) unless there is an exceptionless 1:1 relationship between
group_post_mod_option.option_id and group_post_mod_option.option_name,
results for the latter column will be meaningless because it is not
aggregated. Absent a 1:1 relationship, you need a subquery to fetch the
name.
(ii) the WHERE condition group_post_moderation.group_post_id = 37 will
remove all NULL rows from the right side of the join, turning the OUTER
JOIN effectively into an INNER JOIN, so you might as well write it as an
INNER JOIN
(iii) why not use the alias `count` in the ORDER BY clause (the query
engine would likely spot that)?
So that would give something like...
SELECT
o.option_id,
(SELECT option_name FROM group_post_mod_option.option_name
WHERE option_id = o.option_id) AS name,
COUNT(m.group_post_moderation_option) AS count
FROM group_post_mod_option o
JOIN group_post_moderation m
ON o.option_id = m.group_post_moderation_option
AND m.group_post_id = 37
GROUP BY o.option_id
ORDER BY count, o.option_name;
PB
-----
Naz Gassiep wrote:
I am running this query:
SELECT group_post_mod_option.option_id,
group_post_mod_option.option_name,
COUNT(group_post_moderation.group_post_moderation_option) AS count
FROM group_post_mod_option LEFT OUTER JOIN group_post_moderation ON
(group_post_mod_option.option_id =
group_post_moderation.group_post_moderation_option AND
group_post_moderation.group_post_id = 37) GROUP BY
group_post_mod_option.option_id ORDER BY
COUNT(group_post_moderation.group_post_moderation_option),
group_post_mod_option.option_name;
And getting this error:
'Invalid use of group function'
Without the "ORDER BY" clause, the query works just fine. Anyone know
what's going on?
Regards,
- Naz.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
