***BOGO*** Re: Query - One-to-Many question

[Peter Brawley <peter.brawley@xxxxxxxxxxxxx>]

>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


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=royale@xxxxxxxxxx