CODEWITHSUNDEEP
sqlsqlite
Vassilis De
Vassilis De

Reputation: 373

SQLite "appear the amount of courses for every student whether he is in Attends or not" sql query

I have an ER Diagram as shown below

ER Diagram

for every student I want to appear all courses that addends. So I use query

select studentId,course.courseCode
from student natural left outer join attends
natural left outer join course

which gives me all results in right way

enter image description here

now I want to appear the total amount of courses that a student attends and I am using this query

select studentId,
  (select count(attends.courseCode) 
  from attends natural left outer join student
  )as 'amount'
from student

but I am having this result

enter image description here

How am I supposed to appear the real amount of courses for every student whether he is in Attends or not? That is, a 0 for studentId 6,7,8 and a 2 for studentId 17 etc. Thank you in advance

PS1: If you want more of my tables, please let me know.

PS2: I was not sure about the title. If you find that another title fits better, please suggest

Upvotes: 0

Views: 51

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

First, don't use natural join. It is entirely dependent on the data structure -- and if that changes, then the semantics of the query change too. In other words, you cannot read a query and really understand what it is doing.

Then, for this query, first generate a list of all students and courses using cross join, then bring in the attendance information:

select s.studentId, c.courseCode, count(a.CourseCode)
from student s cross join
     course c left join
     attends a
     on s.studentId = a.studentId and s.courseCode = c.courseCode
group by s.studentId, c.courseCode;

Upvotes: 2

Related Questions