mardi 20 janvier 2015

sqlite show 1 and 0 if record exists or not doesn't work

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


student


attends


course


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


enter image description here


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


whatIget


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