List:General Discussion« Previous MessageNext Message »
From:Tina Matter Date:June 26 2008 7:48pm
Subject:Re: Query - One-to-Many question
View as plain text  
Even if I do a basic select (with no joins) for a given catalog_number, 
I still get two rows back.

Even if I do this simple query, while hardcoding in a catalog_number:

SELECT subject, catalog_number FROM course_subject
WHERE (catalog_number = 520) AND
((subject = 'ME') OR ((course_offer_number = 1) AND (subject NOT LIKE 
'ME')))


I still get two rows back:

subject    catalog_number
ME    520
MSE    520


So I'm not sure what else I need in my Where Clause

Thanks bunches.
Tina


Peter Brawley wrote, On 6/26/08 3:33 PM:
> 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
>>   
>

-- 

*Tina Matter*

Web Software Developer

Department of Mechanical Engineering

University of Michigan

734-763-3184

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