469,270 Members | 1,011 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

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

Filtering resultsets based on GROUP_CONCAT() generated value

$if1 = "IF(s.student_ethnicity_interest_other IS NOT NULL AND
s.student_ethnicity_interest_other != '', " .
" CONCAT(s.student_ethnicity_interest_other, ', ',
GROUP_CONCAT(e.ethnicity_name ORDER BY upper(e.ethnicity_name))), " .
' GROUP_CONCAT(e.ethnicity_name ORDER BY
upper(e.ethnicity_name))' .
" ) AS ethnicity_name \n\n";

This segment of a larger SQL query produces a string that normally
looks like these:

caucasians

caucasians,african-americans

caucasians,african-americans,asian-americans

Nordic peoples,caucasians

"etc"

Here's the problem. Requirement states that we now need to filter the
results by "ethnicity", which is a one-to-many relationship per
applicant.

Let's say you have 20 applicants, and out of those, 1 of those has just
"caucasians" for ethnicity.

When you do the filter which I envision as:

WHERE ... AND ethnicity_name LIKE '%caucasians%'

Instead of having this value in the $result[$i]->ethnicity_name field
resultset value:

caucasians

You see this:

caucasians,caucasians,caucasians,caucasians,caucas ians..[repeated 7
times]

Take out the filter and it displays just fine.

I am not sure how much more info I can provide; the query itself is
about 200 lines long and is under U.S. Government regulated security
(illegal to post it, sorry), unfortunately this is all I can think of
that is allowable to post that illustrates the problem.

Should note there there is an "ethnicity" table with 7 rows in it with
unique ethnicity names like "caucasians", "african-americans", etc.,
and a field in the "applicant" table that is
"applicant_ethnicity_interest_other" where you can enter whatever
ethnicity you want on top of the selected ethnicities in the
"ethnicity" table - if that helps.

Thanx
Phil

Apr 28 '06 #1
0 1119

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by peter stickney | last post: by
2 posts views Thread by Sean | last post: by
1 post views Thread by mstery | 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
By using this site, you agree to our Privacy Policy and Terms of Use.