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.