From: Peter Brawley
Date: June 27 2008 3:55pm
Subject: Re: Query - One-to-Many question
List-Archive: http://lists.mysql.com/mysql/213501
Message-Id: <48650D71.9090301@earthlink.net>
MIME-Version: 1.0
Content-Type: multipart/alternative;
 boundary="------------060708080509050508010504"

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

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
>   

--------------060708080509050508010504--