very complex query produces wrong results

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, ',',
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

FROM student s, completion_comments cc, ethnicity e,
student_ethnicity_interest_assoc seia

WHERE s.id = seia.student_id
AND seia.ethnicity_id = e.id

If I run this query, I do not get all of the results I want, because of
a bizarre data mangling problem:

In student_ethnicity_interest_assoc you can literally have a record
like this:

student_id ethnicity_id
810 0

There is no ID of 0 in ethnicity, thus, the join fails and the record
never shows up, but if I try to join on the ethnicity_id being 0, then
I get a runaway process, Apache, MySQL, everything crashes!

But if I take that very same record out, that record does not appear
even though it should.

I'm at a loss as to how to fix this. Please help!

Thanx
Phil

Can someone help me with this? This is affecting live data so the
matter's a bit urgent.

Re: very complex query produces wrong results

phillip.s.powell@gmail.com wrote:
> 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, ',',
> 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
>
> FROM student s, completion_comments cc, ethnicity e,
> student_ethnicity_interest_assoc seia
>
> WHERE s.id = seia.student_id
> AND seia.ethnicity_id = e.id
>
> If I run this query, I do not get all of the results I want, because of
> a bizarre data mangling problem:
>
> In student_ethnicity_interest_assoc you can literally have a record
> like this:
>
> student_id ethnicity_id
> 810 0
>
> There is no ID of 0 in ethnicity, thus, the join fails and the record
> never shows up, but if I try to join on the ethnicity_id being 0, then
> I get a runaway process, Apache, MySQL, everything crashes!
>
> But if I take that very same record out, that record does not appear
> even though it should.
>
> I'm at a loss as to how to fix this. Please help!
>
> Thanx
> Phil
>
> Can someone help me with this? This is affecting live data so the
> matter's a bit urgent.
>


you might do a LEFT OUTER JOIN to ethnicity that way if it does not
exist, you will still get the student. google searches are repleat with
examples.

Re: very complex query produces wrong results

noone wrote:
> phillip.s.powell@gmail.com wrote:
> > 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, ',',
> > 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
> >
> > FROM student s, completion_comments cc, ethnicity e,
> > student_ethnicity_interest_assoc seia
> >
> > WHERE s.id = seia.student_id
> > AND seia.ethnicity_id = e.id
> >
> > If I run this query, I do not get all of the results I want, because of
> > a bizarre data mangling problem:
> >
> > In student_ethnicity_interest_assoc you can literally have a record
> > like this:
> >
> > student_id ethnicity_id
> > 810 0
> >
> > There is no ID of 0 in ethnicity, thus, the join fails and the record
> > never shows up, but if I try to join on the ethnicity_id being 0, then
> > I get a runaway process, Apache, MySQL, everything crashes!
> >
> > But if I take that very same record out, that record does not appear
> > even though it should.
> >
> > I'm at a loss as to how to fix this. Please help!
> >
> > Thanx
> > Phil
> >
> > Can someone help me with this? This is affecting live data so the
> > matter's a bit urgent.
> >
>
>
> you might do a LEFT OUTER JOIN to ethnicity that way if it does not
> exist, you will still get the student. google searches are repleat with
> examples.

I thought of that, but because of this clause:

SELECT id, student_first_name, student_last_name,
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

FROM student s, completion_comments cc,
student_ethnicity_interest_assoc seia, ethnicity e

----------------------------

If I do a LEFT OUTER JOIN onto ethncity then the value "ethnicity_name"
constantly contains every single ethnicity name found in the ethnicity
table, whether the student selected them or not!

The way it works is simple: A student has a selection of ethnicities to
choose 0 - infinity. If he chooses 0 then he MUST enter an ethnicity
in the "student_ethnicity_interest_other" text field. When we do the
query we must get

1) all students that selected at least 1 ethnicity
2) all students that selected NO ethnicities but entered something in
"student_ethnicity_interest_other"
3) all students that selected at least 1 ethnicity AND entered
something in "student_ethnicity_interest_other"
4) You MUST display, as a single comma-separated string, every single
ethnicity they selected as a SINGLE STRING per every student displayed!

Hence my dilemma!

Phil