Let's say I have these two tables. The students table stores data about the student showing up to the respected class on a specific date. On the contrary, the classes table shows when the respected class started and when was the class last held.
How can I achieve this in postgreSQL 11.
select * from students
student_name student_showed class_name
Jeter 2022-02-05 Math
Jeter 2022-02-06 Math
Jeter 2022-02-07 Math
Jeter 2022-02-08 Math
Jeter 2022-02-09 Math
Jeter 2022-02-05 English
Jeter 2022-02-06 English
Jeter 2022-02-07 English
Jeter 2022-02-08 English
Jeter 2022-02-09 English
Soriano 2022-02-05 Math
Soriano 2022-02-06 Math
Soriano 2022-02-07 Math
Soriano 2022-02-08 Math
Soriano 2022-02-09 Math
Soriano 2022-02-05 English
Soriano 2022-02-06 English
Soriano 2022-02-07 English
Soriano 2022-02-08 English
Soriano 2022-02-09 English
Now for the classes table!
select * from classes
class_name class_started class_held_date
Math 2022-02-05 2022-02-06
English 2022-02-05 2022-02-06
Math 2022-02-05 2022-02-07
English 2022-02-05 2022-02-07
Math 2022-02-05 2022-02-09
English 2022-02-05 2022-02-09
When I do a left-join on the two tables:
select s.*,c.class_started,c.class_held_date from students as s
left join classes as c on s.class_name = c.class_name and s.student_showed = c.class_held_date
student_name student_showed class_name class_started class_held_date
Jeter 2022-02-05 Math
Jeter 2022-02-06 Math 2022-02-05 2022-02-06
Jeter 2022-02-07 Math 2022-02-05 2022-02-07
Jeter 2022-02-08 Math
Jeter 2022-02-09 Math 2022-02-05 2022-02-09
Jeter 2022-02-05 English
Jeter 2022-02-06 English 2022-02-05 2022-02-06
Jeter 2022-02-07 English 2022-02-05 2022-02-07
Jeter 2022-02-08 English
Jeter 2022-02-09 English 2022-02-05 2022-02-09
Soriano 2022-02-05 Math
Soriano 2022-02-06 Math 2022-02-05 2022-02-06
Soriano 2022-02-07 Math 2022-02-05 2022-02-07
Soriano 2022-02-08 Math
Soriano 2022-02-09 Math 2022-02-05 2022-02-09
Soriano 2022-02-05 English
Soriano 2022-02-06 English 2022-02-05 2022-02-06
Soriano 2022-02-07 English 2022-02-05 2022-02-07
Soriano 2022-02-08 English
Soriano 2022-02-09 English 2022-02-05 2022-02-09
Since class_started in the classes tables is a static field, I want it to always show the value rather than show NULLS. I need to show a left join since the student should get credit for showing up to the classes regardless if the classes were held or not.
Can someone please provide insights here.
Result Requested:
student_name student_showed class_name class_started class_held_date
Jeter 2022-02-05 Math 2022-02-05
Jeter 2022-02-06 Math 2022-02-05 2022-02-06
Jeter 2022-02-07 Math 2022-02-05 2022-02-07
Jeter 2022-02-08 Math 2022-02-05
Jeter 2022-02-09 Math 2022-02-05 2022-02-09
Jeter 2022-02-05 English 2022-02-05
Jeter 2022-02-06 English 2022-02-05 2022-02-06
Jeter 2022-02-07 English 2022-02-05 2022-02-07
Jeter 2022-02-08 English 2022-02-05
Jeter 2022-02-09 English 2022-02-05 2022-02-09
Soriano 2022-02-05 Math 2022-02-05
Soriano 2022-02-06 Math 2022-02-05 2022-02-06
Soriano 2022-02-07 Math 2022-02-05 2022-02-07
Soriano 2022-02-08 Math 2022-02-05
Soriano 2022-02-09 Math 2022-02-05 2022-02-09
Soriano 2022-02-05 English 2022-02-05
Soriano 2022-02-06 English 2022-02-05 2022-02-06
Soriano 2022-02-07 English 2022-02-05 2022-02-07
Soriano 2022-02-08 English 2022-02-05
Soriano 2022-02-09 English 2022-02-05 2022-02-09
CodePudding user response：
You should be able to handle this using windowing.
select
student_name,
...
min (c.class_started) over (partition by student_name,class_name)
from
students as s
left join classes as c on s.class_name = c.class_name and s.student_showed = c.class_held_date
CodePudding user response：
Not a sql expert. But here's another way.
select distinct
s.student_name,
s.student_showed,
s.class_name,
(case when c.class_started is NULL then
(select c1.class_started from classes c1 where c1.class_name = s.class_name limit 1)
else c.class_started
end) as "class_started",
c.class_held_date
from students s
left join classes c
on s.student_showed = c.class_held_date