CODEWITHSUNDEEP
sqlpostgresqljoinlateral-join
calben
calben

Reputation: 1358

Using Lateral on Two Tables in Postgresql

Hullo everyone,

I need a little help figuring out how to use lateral across two tables in Postgresql. I have two tables, student, and course, and a function student_course_assessment_info_by_course(student_id, course_id): course_assessment_info that takes the id of a student record and the id of a course record and gets some grade information for that student like their average. I'd like to use this function to make another function called students_near_gpa_minimum that gets a table with student_id,course_id,gpa,minimum_gpa for all students that are within some number of points of the minimum gpa for the course.

So far I understand that I should be able to run the course assessment function "per row" on the student table using the lateral procedure, but I'm having difficulty figuring out how exactly to do that since the assessment function needs both the student id and the course id. The function I have below complains that the course column doesn't exist- which is true. I need to also iterate over the available courses.

select * from 
  (select * from ontrack.student) students,
  lateral (select * from ontrack.student_course_assessment_info_by_course(students."id", "2b0df865-d7c6-4c96-9f10-992cd409dedb")) grades;

I understand this can be solved fairly easily by querying all the data and then calculating the result in a language like C++, but I'm trying to get a proper handle on SQL and I feel like if I understand how to perform this sort of operation it would be hugely beneficial. Then again, maybe this really is something that should be done outside of sql.

Any help is appreciated, thanks!

select student.id, weighted_mark_average
from student
cross join course
cross join lateral course_assessment_info_by_student_id_and_course_id(student."id", course."id");

That works!

Upvotes: 0

Views: 108

Answers (1)

Radim Bača
Radim Bača

Reputation: 10701

You can obtain grades for all combinations of student and courses using your function like this

select * 
from student
cross join course
cross join lateral student_course_assessment_info_by_course(student."id", course."id")

However, it is every combination of course and student. If you are interested only into combinations of courses and students that are actually valid (so the student studied that course) then there must be a course_student table that needs to be joined with student and course.

EDIT

If you are interested into grades of courses that students actually studied then run the following

select s.id, c.id, sc.grade
from student s
join student_course sc on s.id = sc.student_id
join course c on c.id = sc.course_id

Upvotes: 1

Related Questions