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
