List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:June 27 2008 5:32pm
Subject:Re: Query - One-to-Many question
View as plain text  
 >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

Thread
Query - One-to-Many questionTina Matter26 Jun
  • Re: Query - One-to-Many questionPeter Brawley26 Jun
    • Re: Query - One-to-Many questionTina Matter26 Jun
      • Re: Query - One-to-Many questionPeter Brawley26 Jun
        • Re: Query - One-to-Many questionTina Matter26 Jun
          • Re: Query - One-to-Many questionPeter Brawley27 Jun
            • Re: Query - One-to-Many questionTina Matter27 Jun
              • Re: Query - One-to-Many questionPeter Brawley27 Jun
              • Re: Query - One-to-Many questionPeter Brawley27 Jun
                • Re: Query - One-to-Many questionTina Matter27 Jun