locked
orcl query help RRS feed

  • Question

  • User1732249137 posted
    Select DISTINCT l.learnerName, r.STATUS, p.ProgrammeName,pc.Category,c.CourseName
    from learner l, registration r, programme p,programmecoursedetail pc,course c
    where l.learnerid=r.learnerID AND r.programmeid = p.programmeid AND c.courseid = pc.courseid AND r.STATUS='PAID';

    how to display only one learnerName, this query got repick learner name

    Monday, July 22, 2013 11:05 AM

Answers

  • User269602965 posted

    You did not present your data table relationship model,

    so this is a guess on your needs.

    Your problem is that your query is making a Cartesian Product (returning ALL possible, but distinct combinations of all data = MANY ROWS).

    You want to return a specific person, then INNER JOIN is required.

    Describe the relationships between these tables and the Primary Keys and Foreign Keys, and we can help you better refine this script to get one learnerName.

     

    SELECT DISTINCT 
      l.learnerName, 
      r.STATUS, 
      p.ProgrammeName,
      pc.Category,
      c.CourseName
    FROM learner l, 
    INNER JOIN registration          r  ON l.learnerid   = r.learnerID
    INNER JOIN programme             p  ON r.programmeid = p.programmeid
    INNER JOIN course                c  ON p.courseid    = c.courseid
    INNER JOIN programmecoursedetail pc ON c.courseid    = pc.courseid
    WHERE
      r.STATUS='PAID'
    /
    
    /* INNER JOIN course c  ON p.courseid = c.courseid not sure if this matched the data model */
    

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, July 22, 2013 8:56 PM