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
