List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:June 26 2008 7:33pm
Subject:Re: Query - One-to-Many question
View as plain text  
Tina,

 >for some reason, it still pulled all of the rows

Are there multiple rows which meet your WHERE condition? If so, and if 
you want just one of them, your need another WHERE condition.

PB

-----

Tina Matter wrote:
> Peter,
>
> That was the first query I tried, but for some reason, it still pulled 
> all of the rows.  So I've been trying to come up with another solution.
>
> Any other ideas?
>
> Thanks for the reply.
> Tina
>
>
>
> Peter Brawley wrote, On 6/26/08 2:12 PM:
>> Tina
>>
>> >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".
>>
>> Is this what you mean?
>>
>> SELECT ...
>> FROM course_table c
>> JOIN course_subject s ON c.course_id=s.course_id
>> WHERE s.subject="ME" OR (s.course_offer_number=1 AND s.subject <> "ME");
>>
>> PB
>>
>> Tina Matter wrote:
>>> 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
>>>
>>>
>>> ------------------------------------------------------------------------ 
>>>
>>>
>>>
>>> No virus found in this incoming message.
>>> Checked by AVG. Version: 8.0.101 / Virus Database: 270.4.1/1521 - 
>>> Release Date: 6/26/2008 11:20 AM
>>>   
>>
>
> ------------------------------------------------------------------------
>
>
> No virus found in this incoming message.
> Checked by AVG. 
> Version: 8.0.101 / Virus Database: 270.4.1/1521 - Release Date: 6/26/2008 11:20 AM
>   

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