469,927 Members | 1,893 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

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

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.

Apr 10 '06 #1
2 1191
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.
Apr 11 '06 #2

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

Apr 11 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by jimh | last post: by
3 posts views Thread by cassandra.flowers | last post: by
2 posts views Thread by GD | last post: by
10 posts views Thread by jerry.ranch | last post: by
3 posts views Thread by luis.c.torres | last post: by
6 posts views Thread by Jon Bilbao | last post: by
3 posts views Thread by bob laughland | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.