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
