469,128 Members | 1,395 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,128 developers. It's quick & easy.

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

Mar 3 '06 #1
2 1773
<ph**************@gmail.com> wrote in message
news:11**********************@i39g2000cwa.googlegr oups.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.
Mar 4 '06 #2

Bill Karwin wrote:
<ph**************@gmail.com> wrote in message
news:11**********************@i39g2000cwa.googlegr oups.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.


Mar 4 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by gc | last post: by
6 posts views Thread by bettina | last post: by
1 post views Thread by drewmania001 | last post: by
reply views Thread by gmorris | last post: by
bilibytes
1 post views Thread by bilibytes | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.