List:General Discussion« Previous MessageNext Message »
From:Tina Matter Date:June 26 2008 4:41pm
Subject:Query - One-to-Many question
View as plain text  
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, 
and course_offer_number)

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

Thread
Query - One-to-Many questionTina Matter26 Jun
  • Re: Query - One-to-Many questionPeter Brawley26 Jun
    • Re: Query - One-to-Many questionTina Matter26 Jun
      • Re: Query - One-to-Many questionPeter Brawley26 Jun
        • Re: Query - One-to-Many questionTina Matter26 Jun
          • Re: Query - One-to-Many questionPeter Brawley27 Jun
            • Re: Query - One-to-Many questionTina Matter27 Jun
              • Re: Query - One-to-Many questionPeter Brawley27 Jun
              • Re: Query - One-to-Many questionPeter Brawley27 Jun
                • Re: Query - One-to-Many questionTina Matter27 Jun