Strange JOIN query problem

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
I have to produce a query to obtain a single record, however, the
tables I work with have a very strange relationship:

Table: student
 school_type_id (type of school)
 school_year_id (their year in school)
 student_enrollment_status_id (if they're enrollment full time, part
time, whatever)

the "_other" fields contain text the student enters in lieu of not
entering anything that would produce IDs in the "_id" fields.  Like

Table: school_year
id       school_year_name
1        freshman
2        sophomore
3        junior
4        senior

And instead of choosing any of the above, the student enters "King of
the World" in the "_other" text field and thus populating the
"school_year_other" field instead of "school_year_id" (which becomes 0
in this case).

So your student record could look like this:

id     school_type_id     school_type_other     school_year_id
1       4                        NULL                       2

Or it could look like this:

id      school_type_id     school_type_other    school_year_id
1       0                         merchant marine       0

Or any combination of these six fields!

Based on what you see so far, how would you produce a unique query of
one row, knowing that you are grabbing the data blindly, of course,
what on earth do you do?  If I do this:

1) SELECT .. FROM student s, school_year y WHERE s.school_year_id =
Then you might be 0 records if s.school_year = 0


If I do this:

1) SELECT .. FROM student s, school_year y WHERE s.school_year_id NOT
IN (SELECT id from school_year) AND s.school_year_other IS NOT NULL AND
s.school_year_other != ''
Then I get a Cartesian product!!

Basically, I really need help on this and quickly as I have a
presentation in a week and I can't for the life of me figure this query
out (NO DBA's available!)


Re: Strange JOIN query problem

Quoted text here. Click to load it

Here's an example to handle the school year field:

SELECT IF(s.school_year_id = 0, s.school_year_other, y.school_year) AS
FROM student AS s
    LEFT OUTER JOIN school_year AS y ON s.school_year_id =

The outer join ensures it gets the row from s even if there is no matching
row in y.
The IF() function in the select-list chooses the _other label if the _id is
0, otherwise returns the label from y.

Bill K.

Re: Strange JOIN query problem

I'm sorry, but the query fails if school_year_id = 0.  It still
produces an empty set, even though the record exists with
school_year_id = 0.


Bill Karwin wrote:
Quoted text here. Click to load it

Re: Strange JOIN query problem

Ok I got it to work! I had some extraneous JOINS in there by mistake,


Site Timeline