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

[phillip.s.powell]

SELECT s.id, s.student_first _name, s.student_last_ name,
IF(s.student_et hnicity_interes t_other IS NOT NULL AND
s.student_ethni city_interest_o ther != '',
CONCAT(s.studen t_ethnicity_int erest_other, ', ',
e.ethnicity_nam e),
GROUP_CONCAT(DI STINCT e.ethnicity_nam e ORDER BY
upper(e.ethnici ty_name))
) AS ethnicity_name

FROM student s, ethnicity e, student_ethnici ty_interest_ass oc 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_n ame student_last_na me ethnicity_name
1 Phil Powell
hispanics
1 Phil Powell
african-americans

I want this instead:

ID student_first_n ame student_last_na me ethnicity_name
1 Phil Powell
hispanics,afric an-americans

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

Phil

[Bill Karwin]

<ph************ **@gmail.com> wrote in message
news:11******** **************@ i39g2000cwa.goo glegroups.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_nam e

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.

[phillip.s.powell]

Bill Karwin wrote:

<ph************ **@gmail.com> wrote in message
news:11******** **************@ i39g2000cwa.goo glegroups.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_nam e

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.