I have two tables:
1.) A course table (stores course_id and catalog_number)
2.) A course_subject table (stores course_id, catalog_number, subject,
For each row in the course_table, there can be many rows in the
course_subject table, due to cross-postings among different departments.
Here is what I'd like to do.
I would like to grab a list of catalog_numbers from the course_table,
but I only want one row in the course_subject table.
Basically, if the subject is "ME", then I want to select that row. If
there is no row for that catalog_number that has a subject of "ME", then
I want to grab the row that has a course_offer_number of '1' and a
subject that is not equal to "ME".
I am basically only grabbing the subject field from the course_subject
table. If an "ME" subject exists for a catalog_number, grab that
one. Otherwise, grab whatever other one exists. This is assuming
that there will only be one other one.
Does this make sense? I'd really like to know if there's a way to do
this in one query. I can probably do it in PHP with multiple selects,
building my list as I go. But if there's a fancy way to do this in one
query, then I'd much rather do it that way.
Thanks for any help.