From: Peter Brawley
Date: June 27 2008 5:32pm
Subject: Re: Query - One-to-Many question
List-Archive: http://lists.mysql.com/mysql/213506
Message-Id: <48652419.30607@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

 >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.

Ill be offline for awhile so I'll assume answers not available, ie allow 
='ME' dupes and <>'ME' dupes if they exist. A one-query answer is to 
union (i) a query for 'me' matches and (ii) a query for 'non-me' matches 
which excludes 'me' matches:

SELECT c.course_id,s.course_offer_number,s.subject
FROM course_table c
JOIN course_subject s
  ON c.course_id=s.course_id AND s.subject='ME'
UNION
SELECT c.course_id,s.course_offer_number,s.subject
FROM course_table c
JOIN course_subject s
  ON c.course_id=s.course_id AND s.course_offer_number=1 AND s.subject<>'ME'
LEFT JOIN (
  SELECT c.course_id,s.course_offer_number,s.subject
  FROM course_table c
  JOIN course_subject s ON c.course_id=s.course_id AND s.subject='ME'
) m ON c.course_id=m.course_id
WHERE m.course_id IS NULL;

PB