From: Peter Brawley (peter.brawleyearthlink.net)
Date: Fri Jun 27 2008 - 12:32:09 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

>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

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]