Strange JOIN query problem

[phillip.s.powell]

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

Table: student
Fields:
id
first_name
last_name
email
school_type_id (type of school)
school_type_other
school_year_id (their year in school)
school_year_other
student_enrollment_status_id (if they're enrollment full time, part
time, whatever)
student_enrollment_status_other
unique_key

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

Table: school_year
Fields
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
school_year_other
1 4 NULL 2
NULL

Or it could look like this:

id school_type_id school_type_other school_year_id
school_year_other
1 0 merchant marine 0
pfc

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 =
y.id
(etc.)
Then you might be 0 records if s.school_year = 0

BUT

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!)

Thanx
Phil

[Bill Karwin]

<ph**************@gmail.com> wrote in message
news:11*********************@g47g2000cwa.googlegro ups.com...

. . . 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?

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
school_year
FROM student AS s
LEFT OUTER JOIN school_year AS y ON s.school_year_id = y.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.

Regards,
Bill K.

[phillip.s.powell]

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.

Phil

Bill Karwin wrote:

<ph**************@gmail.com> wrote in message
news:11*********************@g47g2000cwa.googlegro ups.com...

. . . 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?

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
school_year
FROM student AS s
LEFT OUTER JOIN school_year AS y ON s.school_year_id = y.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.

Regards,
Bill K.

[phillip.s.powell]

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

Phil