postgresql - How to query the data in a join table by two sets of joined records? -

i've got 3 tables: users, courses, , grades, latter of joins users , courses metadata user's score course. i've created sqlfiddle, though site doesn't appear working @ moment. schema looks this:

create table users(   id int,   name varchar,   primary key (id) ); insert users values   (1, 'beth'),   (2, 'alice'),   (3, 'charles'),   (4, 'dave');  create table courses(   id int,   title varchar,   primary key (id) ); insert courses values   (1, 'biology'),   (2, 'algebra'),   (3, 'chemistry'),   (4, 'data science');  create table grades(   id int,   user_id int,   course_id int,   score int,   primary key (id) ); insert grades values   (1, 2, 2, 89),   (2, 2, 1, 92),   (3, 1, 1, 93),   (4, 1, 3, 88); 

i'd know how (if possible) construct query specifies users.id values (1, 2, 3) , courses.id values (1, 2, 3) , returns users' grades.score values courses

| name    | algebra | biology | chemistry | |---------|---------|---------|-----------| | alice   |      89 |      92 |           | | beth    |         |      93 |        88 |  | charles |         |         |           | 

in application logic, i'll receiving array of user_ids , course_ids, query needs select users , courses dynamically primary key. (the actual data set contains millions of users , tens of thousands of courses—the examples above sample work with.)

ideally, query would:

• use course titles dynamic attributes/column headers users' score data
• sort row , column headers alphabetically
• include empty/null cells if user-course pair has no grades relationship

i suspect may need combination of joins , postgresql's crosstab, can't quite wrap head around it.

update: learning terminology "dynamic pivot", found this answer appears trying solve related problem in postgres crosstab()

i think simple pivot query should work here, since have 4 courses in data set pivot.

select t1.name,        max(case when t3.title = 'biology'      t2.score else null end) biology,        max(case when t3.title = 'algebra'      t2.score else null end) algebra,        max(case when t3.title = 'chemistry'    t2.score else null end) chemistry,        max(case when t3.title = 'data science' t2.score else null end) data_science users t1 left join grades t2     on t1.id = t2.user_id left join courses t3     on t2.course_id = t3.id group t1.name 

follow link below running demo. used mysql because, have noticed, sqlfiddle seems perpetually busted other databases.

sqlfiddle