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
>
