I would like to take a step further my former question So, supposing we have some students who attend some courses at specific time periods, we get those tables
 
 
 
where fields in bold are the primary keys
In Attends table, attendsId PK, (stRegNum, courseCode,periodId) FK
For Student with id = 00001 I want to show all courses who attends with a 1 before of them and a 0 for those who doesn't. Like this
 
I created a query to see how it works
select (
case when exists(
select 1
from course
where attends.stRegNum = '00001'
group by course.courseCode
)
then 1
else 0
end
) as hh, *
from course left join attends on course.courseCode = attends.courseCode
and I get this
 
which is obviously wrong. I know that left join messes it up, but everything I tried, I couldn't get the expected results.
Could someone please tell me where I'm doint wrong, and how to fix it? Thank you in advance.
PS: I am not sure about the title. Please do suggest if you have a better idea!
Aucun commentaire:
Enregistrer un commentaire