From: Peter Brawley
Date: June 26 2008 6:12pm
Subject: Re: Query - One-to-Many question
List-Archive: http://lists.mysql.com/mysql/213476
Message-Id: <4863DC05.9040205@earthlink.net>
MIME-Version: 1.0
Content-Type: multipart/alternative;
 boundary="------------060306020303070302040108"

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

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
>   

--------------060306020303070302040108--