Geek Answers Handbook

SQL: INNER JOIN + NOT EXIST

I am trying to create a SQL statement where I need to join 3 tables

Enrollstudents

EnrollID UserID SubjID 1 1 1 2 1 2 3 1 3 4 3 1 5 7 2

Students

 StudentID UserID YearID 1 1 1 2 3 1 3 7 1

Subjects

 SubjID SubjCode YearID 1 English 1 2 Math 1 3 Science 1

and the exit should be ...

 UserID 2 3

since User 1 has already registered all items, while User 3 and User 7 are still displayed, as some items are still not registered.

I have the following SQL statement with no luck:

 SELECT Students.UserID FROM Students WHERE Students.YearID = 1 AND Students.UserID NOT IN (SELECT EnrollStudents.UserID FROM EnrollStudents)

Any ideas?

+4
source share
3 answers
 SELECT s.UserID FROM Students AS s LEFT OUTER JOIN EnrollStudents AS es ON s.UserID = es.UserID GROUP BY s.UserID HAVING COUNT(DISTINCT(es.SubjID)) < (SELECT COUNT(*) FROM Subjects)

... wait a minute. I think you mix your "StudentID" and "UserID" in your output example and the EnrollStudents table.

http://sqlfiddle.com/#!3/61618/1

+4
source 
 select s.UserID from Students s left outer join ( select UserID from EnrollStudents group by UserID having count(distinct SubjID) = 3 ) a on s.UserID = a.UserID where a.UserID is null and s.YearID = 1

SQL script example

+1
source

It seems that you are trying to qualify all students in the first year of study who did not participate in all classes that require first year, so you want to get only students 2 and 3. Your data has everything in the group for one year ID, but I suspect that you actually have data that spans several years, and you are clearly interested only in these students in the 1st year, and those topics that are ALSO related to the requirements of the first year.

The results of the first query (YrSubjects) will pre-aggregate the number of classes in one year, so you do not need to do this again for each student. Only once ... With this, like an unassigned JOIN, the rest of the request will be Cartesian, but one record per person, at least duplicate.

The rest of the tables / joins follow. Get students who are registered in subjects that are related only to the 1st year. The where clause explicitly restricts only those who are "1st year."

The last HAVING sentence applies the number of credits that are LESS than the general subjects for the requirements of the first year. With this query you are not "fixed" in a certain hard-coded number of objects that you are EXPECTING ...

 SELECT S.StudentID FROM ( select count(*) as YrClasses from Subjects where YearID = 1 ) YrSubjects, Students S JOIN EnrollStudents ES on S.UserID = ES.UserID JOIN Subjects S ON ES.SubjID = S.SubjID AND S.YearID = 1 WHERE S.YearID = 1 HAVING count(*) < YrSubjects.YrClasses
+1
source

More articles:


All Articles