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

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