-1

Hello I'm trying to run a particular query for my final year project where I want to find out the number of undergraduate students studying computer science in 2014 & 2016. The tables I'm using are coursesEnrollments & courses to record the number of students enrolled on the Computer Science course.

I got help from my supervisor and he suggested me of using self-join on courseEnrollments just so that it's easier find the same CS enrollments for students enrolled in 2014 & 2016. The query below shows you how I wrote it:

SELECT COUNT(*) 
FROM courseEnrollment 
WHERE StudentID IN 
  (SELECT X.StudentID 
   FROM courseEnrollment Y
   JOIN courseEnrollment X
   ON Y.courseID = X.courseID
   JOIN courses C
   ON Y.courseID = C.courseID
   WHERE C.courseName = 'Computer Science'
   AND X.StartAcademicYear = 2014 
   AND Y.StartAcademicYear = 2016);

When I ran it on MySQL, I get the wrong result (which is 4 not 6) because it only counts the students enrolled on Computer science in 2014 & not 2016. Please tell me how I can fix this problem. Thank you!

P.S. I only added "X.StudentID" in the inner query to stop MySQL from getting confused with the StudentID attribute.Changing it to "Y.StudentID" gives me 2. Again, I want to know why it's not counting all those 6 students (2 enrolled in 2016 & 4 enrolled in 2014) that are enrolled on Computer Science.

Magan
  • 1
  • 3
  • Probably you should make sure, that student from X and student from Y are the same person. – michaJlS Feb 23 '17 at 21:32
  • how? doesn't the inner join do that for me? – Magan Feb 23 '17 at 21:41
  • Do you have students that were enrolled in 2014 **and** 2016? – PM 77-1 Feb 23 '17 at 21:41
  • @Magan in the `ON` clause you define what is the condition (constraint) for joining. – michaJlS Feb 23 '17 at 21:42
  • @PM 77-1 Yes I do have students enrolled in both 2014 & 2016. these data are stored in courseEnrollment table. – Magan Feb 23 '17 at 21:44
  • @michaJIS how do I make sure that Students from courseEnrolled X & Y are the same person? – Magan Feb 23 '17 at 21:47
  • Let say, you have just one student in the table. And this student was enrolled in 2014 and 2016. What is the correct result: 1 or 2? – PM 77-1 Feb 23 '17 at 21:47
  • I'm not sure how this would work because you're only enrolled once to any course in university. Basically I'm doing this query to record the number of course enrollments to computer science in both 2014 & 2016. Computer Science in my university i.e Queen Mary University of London in the UK is a 3rd year course so what I want is to find out how many students enrolled to that course back in 2014 and 2016. Do you understand my problem? the years refer to the start academic year. – Magan Feb 23 '17 at 21:53
  • I believe I understand now. Check out my answer. Does it solve your problem? – PM 77-1 Feb 23 '17 at 21:59
  • Thank you lol that was helpful. Again, I thought using a self join would have been useful. – Magan Feb 23 '17 at 22:04

1 Answers1

1

If any student could have taken this course only once:

SELECT COUNT(*)  
FROM courseEnrollment ce
JOIN courses c ON ce.courseid=c.courseid
WHERE c.courseName = 'Computer Science' AND
      ce.startAcademicYear IN (2014, 2016)
PM 77-1
  • 12,933
  • 21
  • 68
  • 111
  • Do you think using a self join is appropriate for courseEnrollment? – Magan Feb 23 '17 at 22:00
  • I do not see why you need it. Possibly I still misunderstand your question. Does my query return the result you are looking for? – PM 77-1 Feb 23 '17 at 22:02
  • it does because in my database there are 6 students enrolled to Computer Science e.g. 2 from 2016 as they are in their first year and 4 from 2014 are in their final year currently. my final year project was meant to be a data warehouse where those tables would join the fact table but right now it's useless lol. I'll talk to my supervisor about it. – Magan Feb 23 '17 at 22:09
  • If you are interested in SELF JOINs and are still shaky in SQL, I suggest you to work through [SQLZoo](http://sqlzoo.net/wiki/The_JOIN_operation). – PM 77-1 Feb 23 '17 at 22:15