By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,631 Members | 892 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,631 IT Pros & Developers. It's quick & easy.

Strange JOIN query problem

P: n/a
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

Jan 27 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
<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.
Jan 27 '06 #2

P: n/a
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.


Jan 27 '06 #3

P: n/a
Ok I got it to work! I had some extraneous JOINS in there by mistake,
THANX!!

Phil

Jan 27 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.