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
