Tina
>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.
I see. Then to complete spec, what behaviour is desired when there are
two rows with 'ME', or two rows with (course_offer_number = 1 AND
subject NOT LIKE 'ME')?
>I get the same results with these two statements:
>(subject NOT LIKE 'ME')
>(subject <> 'ME')
>So I didn't really see a difference.
Indeed. The difference is efficiency. To see the effect, try it on a
large table.
PB
-----
Tina Matter wrote:
> 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
>>>
>>
>
> ------------------------------------------------------------------------
>
>
> No virus found in this incoming message.
> Checked by AVG.
> Version: 8.0.101 / Virus Database: 270.4.1/1522 - Release Date: 6/27/2008 8:27 AM
>
