noone wrote:
ph**************@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
