GROUP_CONCAT() produces fatal error in MySQL 4.1.12 - time-sensitive issue

SELECT s.id, s.student_first_name, s.student_last_name,
IF(s.student_ethnicity_interest_other IS NOT NULL AND
s.student_ethnicity_interest_other != '',
CONCAT(s.student_ethnicity_interest_other, ', ',
e.ethnicity_name),
GROUP_CONCAT(DISTINCT e.ethnicity_name ORDER BY
upper(e.ethnicity_name))
) AS ethnicity_name

FROM student s, ethnicity e, student_ethnicity_interest_assoc seia
WHERE s.id = seia.student_id
AND seia.ethnicity_id = e.id

GROUP BY s.id, s.student_first_name, s.student_last_name,
ethnicity_name
ORDER BY upper(ethnicity_name) ASC

This query produces the following error:

Can't group on 'ethnicity_name' using query: SELECT s.id...

I am trying to prevent this from happening:

ID student_first_name student_last_name ethnicity_name
1 Phil Powell
hispanics
1 Phil Powell
african-americans

I want this instead:

ID student_first_name student_last_name ethnicity_name
1 Phil Powell
hispanics,african-americans

Please help, this is time-sensitive as this is unfortunately live

Phil

Re: GROUP_CONCAT() produces fatal error in MySQL 4.1.12 - time-sensitive issue

wrote in message
news:1141429107.147255.184220@i39g2000cwa.googlegroups.com.. .
> Can't group on 'ethnicity_name' using query: SELECT s.id...

First, you need to specify the table alias in the GROUP BY clause, or else
your usage of ethnicity_name is ambiguous:

GROUP BY s.id, s.student_first_name, s.student_last_name, e.ethnicity_name

Second, you need to remove ethnicity_name from your GROUP BY clause, or else
you it doesn't make sense to use it in the GROUP_CONCAT function. You'll
always get each value of the ethnicity on separate rows anyway. That's how
GROUP BY works.

GROUP BY s.id, s.student_first_name, s.student_last_name

Regards,
Bill K.

Re: GROUP_CONCAT() produces fatal error in MySQL 4.1.12 - time-sensitive issue

Bill Karwin wrote:
> wrote in message
> news:1141429107.147255.184220@i39g2000cwa.googlegroups.com.. .
> > Can't group on 'ethnicity_name' using query: SELECT s.id...
>
> First, you need to specify the table alias in the GROUP BY clause, or else
> your usage of ethnicity_name is ambiguous:
>
> GROUP BY s.id, s.student_first_name, s.student_last_name, e.ethnicity_name
>
> Second, you need to remove ethnicity_name from your GROUP BY clause, or else
> you it doesn't make sense to use it in the GROUP_CONCAT function. You'll
> always get each value of the ethnicity on separate rows anyway. That's how
> GROUP BY works.
>
> GROUP BY s.id, s.student_first_name, s.student_last_name

That did it! AT LAST!!!!!!!!! Now if only I understood what you did :(
Thanx!!

Phil

>
> Regards,
> Bill K.