From: Peter Brawley
Date: June 26 2008 10:11pm
Subject: Re: Query - One-to-Many question
List-Archive: http://lists.mysql.com/mysql/213481
Message-Id: <4864142C.4090000@earthlink.net>
MIME-Version: 1.0
Content-Type: multipart/alternative;
 boundary="------------070606090000080901000601"

--------------070606090000080901000601
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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
>   

--------------070606090000080901000601--