Consider the following tables:

Student
-------
id
name

and

Assignment
----------
id
name

and

Grade
-----
id
student_id
assignment_id
grade

A student can have multiple grades, corresponding to different assignments.

Now, I want to select records to generate a table that looks like this:

Name      Assignment 1      Assignment 2    Assignment 3
--------------------------------------------------------
Bob       55%               80%             23%
Suzy      90%               65%             100%

And, I want to be able to sort by one of the grades (i.e. Assignment 1)

Is this something which can be done with SQL? As an added bonus, can this be done with flask-sqlalchemy?

I have an idea I need to do a JOIN and ORDER BY but I don't really know where to go with this.

Thanks!

If you are using SQLite, you have to use the lowest common denominator of SQL, meaning a join for every single assignment you want to consider:

SELECT
  name,
  Grade1.grade AS assignment1,
  Grade2.grade AS assignment2,
  Grade3.grade AS assignment3
FROM
  Student
  LEFT JOIN Grade AS Grade1 ON (Student.id = Grade1.student_id and Grade1.assignment_id = ...)
  LEFT JOIN Grade AS Grade2 ON (Student.id = Grade2.student_id and Grade2.assignment_id = ...)
  LEFT JOIN Grade AS Grade3 ON (Student.id = Grade3.student_id and Grade3.assignment_id = ...)
ORDER BY Grade1.grade

Translating this to SQLAlchemy should be straightforward.

more