List:General Discussion« Previous MessageNext Message »
From:Tina Matter Date:June 27 2008 12:37pm
Subject:Re: Query - One-to-Many question
View as plain text  
Peter,

Yes, I know there are two rows in the course_subject table with a 
catalog_number of 520.   One has a subject of 'ME' and the other has a 
subject of 'MSE'.
What I want is ONLY the 'ME' row (if a row exists with a subject of 
'ME').    If an 'ME' subject row does not exist, then I want the other one.
This is just one example.  There are other catalog_numbers that will 
have two rows.  There may also be catalog_numbers that only show one row 
(that does not have a subject of 'ME').

FYI:

I get the same results with these two statements:

(subject NOT LIKE 'ME')
(subject <> 'ME')

So I didn't really see a difference.

Thank you.
Tina



Peter Brawley wrote, On 6/26/08 6:11 PM:
> Tina,
>
> >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')))
>
> Errrm, you mean ...subject <> 'ME'..., don't you!?
>
> >I still get two rows back...
> >So I'm not sure what else I need in my Where Clause
>
> You said the query spec was rows with a given catalog_number AND 
> (subject='ME' OR (course_offer_number of '1' and subject <> 'ME')). 
> There are two such rows for catalog number 520. What do you want your 
> query to return in that instance?
>
> PB
>
> -----
>
> Tina Matter wrote:
>> 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
>>>>   
>>>
>>
>> ------------------------------------------------------------------------
>>
>>
>> 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