Among 30 students, one chooses three preferred partner. I have to show students that chose each other as partner. For example,
student_id = 1, partner_id = 2
student_id = 2, partner_id = 1. This should show on the webpage, “(student_id1, student_id2)”.

Also, their fav courses(among 5) should be displayed next to their names.

My problem is it is showing like this:

student_id1, student_1, course_id1
student_id1, student_1, course_id2
student_id1, student_1, course_id3
student_id1, student_1, course_id4
student_id1, student_1, course_id5

I’m using Flask application.

This is how my app.py looks like:

@app.route("/summary/page")
def summary():
     cursor = get_db().cursor()
    
     cursor.execute("""
         SELECT student.first_name AS student_first_name, 
                student.last_name AS student_last_name, 
                student.first_name AS partner_first_name, 
                student.last_name AS partner_last_name, 
                course.title AS course_title
         FROM pair p, student, course
         JOIN student s1 ON p.student_id = s1.id
         JOIN student s2 ON p.partner_id = s2.id
         JOIN course c ON student.pcourse_id = c.id
     """)
     pairs = cursor.fetchall()

This is how my html code looks like.

{% for pair in pairs %}
          <tr>
            <td>{{ pair.student_first_name }} {{ pair.student_last_name }}</td>
            <td>{{ pair.partner_first_name }} {{ pair.partner_last_name }}</td>
            <td>{{ pair.course_title }}</td>
          </tr>
          {% endfor %}

Notes: 1. In the code, there is considered to display only one course. Need to change it so that it shows both partners’ fav course.
2. student table [‘id’, ‘first_name’, ‘last_name’, ‘class_id’, ‘favcourse_id’]
3. pair table[‘student_id’, ‘partner_id’]

I’m stuck at writing SQL statement. Any help would be appreciated. Open to give details if anything is not clear.