>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/[EMAIL PROTECTED]
