Too many results from query

I'm trying to both understand the fol开发者_C百科lowing query,

SELECT s.LAST_NAME||', '||s.FIRST_NAME||' '||COALESCE(s.MIDDLE_NAME,' ') AS FULL_NAME,
s.LAST_NAME,
s.FIRST_NAME,
s.MIDDLE_NAME,
s.STUDENT_ID,
ssm.SCHOOL_ID,
ssm.SCHOOL_ID AS LIST_SCHOOL_ID,
ssm.GRADE_ID ,
sg1.BENCHMARK_ID,
sg1.GRADE_TITLE,
sg1.COMMENT AS COMMENT_TITLE,
ssm.STUDENT_ID,
sg1.MARKING_PERIOD_ID,
sg1.LONGER_COURSE_COMMENTS,
sp.SORT_ORDER,
sched.COURSE_PERIOD_ID 
FROM STUDENTS s,
STUDENT_ENROLLMENT ssm ,
SCHEDULE sched 
LEFT OUTER JOIN STUDENT_REPORT_CARD_BENCHMARKS sg1 ON (
sg1.STUDENT_ID=sched.STUDENT_ID 
AND sched.COURSE_PERIOD_ID=sg1.COURSE_PERIOD_ID 
AND sg1.MARKING_PERIOD_ID IN ('0','442','445','450') 
AND sg1.SYEAR=sched.SYEAR) 
LEFT OUTER JOIN COURSE_PERIODS rc_cp ON (
rc_cp.COURSE_PERIOD_ID=sg1.COURSE_PERIOD_ID 
AND rc_cp.DOES_GRADES='Y') 
LEFT OUTER JOIN SCHOOL_PERIODS sp ON (sp.PERIOD_ID=rc_cp.PERIOD_ID) 
WHERE ssm.STUDENT_ID=s.STUDENT_ID  
AND ssm.SCHOOL_ID='1'  
AND ssm.SYEAR='2010' 
AND ('22-APR-11' BETWEEN ssm.START_DATE AND ssm.END_DATE OR (ssm.END_DATE IS NULL))  
AND (LOWER(s.LAST_NAME) LIKE 'la''porsha%' OR LOWER(s.FIRST_NAME) LIKE 'la''porsha%' ) 
AND sched.STUDENT_ID=ssm.STUDENT_ID AND sched.MARKING_PERIOD_ID IN ('0','444','446','447','445','448','450','443','449') 
AND ('22-APR-11' BETWEEN sched.START_DATE AND sched.END_DATE OR (sched.END_DATE IS NULL AND '22-APR-11'>=sched.START_DATE))     
ORDER BY s.LAST_NAME,s.FIRST_NAME

and modify it to return the correct results - to only return one distinct person. When any particular person is searched for, multiple results are returned because there are unique values returned from schedule.course_period_id. As there are several left outer joins on the course_period_id field but across different tables, I'm confused as to where to modify the query.

My attempt to help people answer by formatting your query and getting rid of the mixed syntax. Not really an answer but too long for a comment:

    SELECT s.LAST_NAME || ', ' || s.FIRST_NAME || ' ' || COALESCE(s.MIDDLE_NAME,' ') 
      AS FULL_NAME,
    s.LAST_NAME, s.FIRST_NAME, s.MIDDLE_NAME, s.STUDENT_ID, 
    ssm.SCHOOL_ID, ssm.SCHOOL_ID AS LIST_SCHOOL_ID, ssm.GRADE_ID ,
    sg1.BENCHMARK_ID, sg1.GRADE_TITLE, sg1.COMMENT AS COMMENT_TITLE,
    ssm.STUDENT_ID, sg1.MARKING_PERIOD_ID, sg1.LONGER_COURSE_COMMENTS,
    sp.SORT_ORDER, sched.COURSE_PERIOD_ID 
    FROM STUDENTS s
      INNER JOIN STUDENT_ENROLLMENT ssm
        ON ssm.STUDENT_ID=s.STUDENT_ID   -- moved from WHERE to here
      INNER JOIN SCHEDULE sched 
        ON sched.STUDENT_ID=ssm.STUDENT_ID -- moved from WHERE to here
      LEFT OUTER JOIN STUDENT_REPORT_CARD_BENCHMARKS sg1 
        ON (    sg1.STUDENT_ID=sched.STUDENT_ID 
            AND sched.COURSE_PERIOD_ID=sg1.COURSE_PERIOD_ID 
            AND sg1.MARKING_PERIOD_ID IN ('0','442','445','450') 
            AND sg1.SYEAR=sched.SYEAR) 
      LEFT OUTER JOIN COURSE_PERIODS rc_cp 
        ON (    rc_cp.COURSE_PERIOD_ID=sg1.COURSE_PERIOD_ID 
            AND rc_cp.DOES_GRADES='Y') 
      LEFT OUTER JOIN SCHOOL_PERIODS sp 
        ON (sp.PERIOD_ID=rc_cp.PERIOD_ID) 
    WHERE ssm.SCHOOL_ID='1'  
      AND ssm.SYEAR='2010' 
      AND ('22-APR-11' BETWEEN ssm.START_DATE AND ssm.END_DATE 
           OR (ssm.END_DATE IS NULL))  
      AND (  LOWER(s.LAST_NAME) LIKE 'la''porsha%' 
           OR LOWER(s.FIRST_NAME) LIKE 'la''porsha%' ) 
    AND sched.MARKING_PERIOD_ID 
         IN  ('0','444','446','447','445','448','450','443','449') 
    AND (   '22-APR-11' BETWEEN sched.START_DATE AND sched.END_DATE 
         OR (     sched.END_DATE IS NULL
             AND '22-APR-11' >= sched.START_DATE))     
ORDER BY s.LAST_NAME, s.FIRST_NAME

Hope it helps.

Well of course you have mulitple records if the child tables joined to have multiple records for the same person. That is expected and correct behavior.

If you only want one record per person, then you must modify the query to tell it which of the multiple child records you want it to choose. But why wouldn't you want to see all the scheduled courses for the person, instead of only one?

If you must you coudl use group by and then put an aggregate (like min or max) on the fields which are causing you the multiple records. However, you would still need to know if you only want the first period records or the last period records or how would you decide out of six records for the person which one you want to see?

Look up the group by clause.