live data affected by bad query - urgent help

ppowell

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

TF_TheMoon

First you have four tablesd and only two joins, that's going to cause your query to get some odd results and run pretty slow. You should also join tables in the FROM clause, as per the ANSI standard, as its more optimised.

But to your questions. if the table student_ethnicity_interest_assoc has an entry of 0 what do you want this to join to?

ppowell

I will have to give you a bit more information then:

There has to be an entry in student_ethnicity_interest_assoc, even if the student never selected any of the given ethnicities, this is due to the GROUP_CONCAT() function inside the IF() statement in the selection clause. However, since it can't be an existing ethnicity, it must be 0.

Here is more information:

WHERE s.has_completion_reference_letter1 = cc.has_reference_letter1
AND s.has_completion_reference_letter2 = cc.has_reference_letter2
AND s.has_school_transcript = cc.has_school_transcript
AND s.id = seia.student_id AND seia.ethnicity_id = e.id

Phil