Peter,
I really appreciate all the help. Unfortunately, the query you came up
with still returns two rows for catalog_number = 520.
I modified your query slightly to this to qualify a specific catalog_number:
SELECT c.course_id,s.course_offer_number,s.subject
FROM course c
JOIN course_subject s
ON c.course_id=s.course_id AND s.subject='ME' AND c.catalog_number = 520
UNION
SELECT c.course_id,s.course_offer_number,s.subject
FROM course c
JOIN course_subject s
ON c.course_id=s.course_id AND s.course_offer_number=1 AND
s.subject<>'ME' AND c.catalog_number = 520
LEFT JOIN (
SELECT c.course_id,s.course_offer_number,s.subject
FROM course c
JOIN course_subject s ON c.course_id=s.course_id AND s.subject='ME'
AND c.catalog_number = 520
) m ON c.course_id=m.course_id
WHERE m.course_id IS NULL
I guess I suppose that there could be multiple 'ME' rows for a
particular catalog_number, with different course_offer_numbers. But
basically, I only want to grab one subject value. I want it to be 'ME'
if a row exists with that subject. Otherwise, grab whatever subject the
catalog_number has that's not equal to 'ME' and that has a
course_offer_number of 1. Not really sure what to do if there are
multiple. I guess for the time being, we are assuming that if there is
no 'ME' row, then there will be only one other row for that catalog_number.
Hmmmm......I'm starting to think that what I'm asking for is not doable
in one select statement.
Thanks bunches.
Tina
Peter Brawley wrote, On 6/27/08 1:32 PM:
>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
--
*Tina Matter*
Web Software Developer
Department of Mechanical Engineering
University of Michigan
734-763-3184
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
