May 4, 2017 at 3:27 pm
I have this question selecting all students that are registered to a course.
Select distinct Registrations.CourseId AS 'KursID', Course.CourseName AS 'Kursnamn', Student.StudentId As 'SID'
From Student
INNER JOIN
Registrations on
Registrations.StudentId = Student.StudentId
Inner Join
Course on
Course.CourseId = Registrations.CourseId
where Registrations.StudentId = 3
Now I can see what courses that student with studentId = 3 is attending.
What I want is to see all the courses the student is NOT attending.
May 4, 2017 at 4:11 pm
Roughly:
SELECT whatever you need FROM Courses WHERE CourseID NOT IN (Select the courseIDs from Registrations for that student)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 7, 2017 at 2:21 am
Thanks!
I have the table registrations which is connection table.
Here you can see what students attend what courses.
So I tried this:
Select Registrations.CourseId AS 'KursID', Course.CourseName AS 'Kursnamn', Person.FName AS 'Lärarens förnamn', Person.SName AS 'Efternamn', person.StudentId
From Registrations
INNER JOIN
Course on
Course.CourseId = Registrations.CourseId
Inner Join
Teacher on
Course.TeacherId = Teacher.TeacherId
Inner Join
Person on
Teacher.TeacherId = Person.TeacherId
WHERE Registrations.StudentId NOT IN (
SELECT Registrations.StudentId
FROM Registrations
Where Registrations.StudentId = 3
)
That gives me all courses that student with ID = 3 doesn't attend. But since course 1 for example still have other students, it still displays.
I would like to say:
"show me all courses in registrations but not the courses where Student 3 attends"
May 7, 2017 at 5:15 am
From your data, how do you know what courses Student 3 attends? Is there a StudentID column in Courses as well,or does a student attend all courses within a registration? We can't tell this from your SQL.
If the latter, then your above SQL should work; if the former, change your NOT IN to look at Courses, not registrations.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 7, 2017 at 5:50 am
larsp777se - Sunday, May 7, 2017 2:21 AMThat gives me all courses that student with ID = 3 doesn't attend. But since course 1 for example still have other students, it still displays.I would like to say:
"show me all courses in registrations but not the courses where Student 3 attends"
Looks at the difference between what I posted and your code.
My suggestion:
WHERE CourseID NOT IN (Select the courseIDs from Registrations for that student)
Your post:
WHERE Registrations.StudentId NOT IN (
What I suggested will do what you asked for
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 7, 2017 at 9:41 am
Thom A - Sunday, May 7, 2017 5:15 AMFrom your data, how do you know what courses Student 3 attends? Is there a StudentID column in Courses as well,or does a student attend all courses within a registration? We can't tell this from your SQL.If the latter, then your above SQL should work; if the former, change your NOT IN to look at Courses, not registrations.
There is no StudentID in Courses. It's in the registration-table you can see what courses a student is inrolled in.
That is a connection table.
Like.
studentID CourseID
1 5
3 5
2 4
3 2
4 2
So I would like to display all courses where Student 3 is not enrolled but not all rows. So I would not like to show row nr 1 or row nr 4 since they attend the same couses as student 3
May 7, 2017 at 10:03 am
Thom A - Sunday, May 7, 2017 5:15 AMFrom your data, how do you know what courses Student 3 attends? Is there a StudentID column in Courses as well,or does a student attend all courses within a registration? We can't tell this from your SQL.If the latter, then your above SQL should work; if the former, change your NOT IN to look at Courses, not registrations.
There is no StudentID in Courses. It's in the registration-table you can see what courses a student is inrolled in.
That is a connection table.
Like.
studentID CourseID
1 5
3 5
2 4
3 2
4 2
So I would like to display all courses where Student 3 is not enrolled but not all rows. So I would not like to show row nr 1 or row nr 4 since they attend the same couses as student 3
GilaMonster - Sunday, May 7, 2017 5:50 AMlarsp777se - Sunday, May 7, 2017 2:21 AMThat gives me all courses that student with ID = 3 doesn't attend. But since course 1 for example still have other students, it still displays.I would like to say:
"show me all courses in registrations but not the courses where Student 3 attends"
Looks at the difference between what I posted and your code.
My suggestion:
WHERE CourseID NOT IN (Select the courseIDs from Registrations for that student)Your post:
WHERE Registrations.StudentId NOT IN (What I suggested will do what you asked for
Ok, so I tried this. Not sure how to solve ""Select the courseIDs from Registrations for that student)"
elect Registrations.CourseId AS 'KursID', Course.CourseName AS 'Kursnamn', Person.FName AS 'Lärarens förnamn', Person.SName AS 'Efternamn'
From Registrations
INNER JOIN
Course on
Course.CourseId = Registrations.CourseId
Inner Join
Teacher on
Course.TeacherId = Teacher.TeacherId
Inner Join
Person on
Teacher.TeacherId = Person.TeacherId
WHERE Registrations.CourseId NOT IN (
SELECT *
FROM Registrations
Where Registrations.CourseId = 3
)
May 7, 2017 at 10:11 am
Now this:
Select Registrations.CourseId AS 'KursID', Course.CourseName AS 'Kursnamn', Person.FName AS 'Lärarens förnamn', Person.SName AS 'Efternamn'
From Registrations
INNER JOIN
Course on
Course.CourseId = Registrations.CourseId
Inner Join
Teacher on
Course.TeacherId = Teacher.TeacherId
Inner Join
Person on
Teacher.TeacherId = Person.TeacherId
WHERE Registrations.CourseId NOT IN (
SELECT Registrations.StudentId
FROM Registrations
Where Registrations.StudentId = 3
)
May 7, 2017 at 2:44 pm
WHERE Registrations.CourseId NOT IN (
SELECT Registrations.StudentId
Is that ever going to work?
That's like saying "Give me all the employees who have the same name as the company's departments"
You compare courseIDs to CourseIDs, and StudentIDs to StudentIDs. Comparing a CourseID to a StudentID as you did is completely meaningless.
Right operator, but wrong column.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 8, 2017 at 11:51 pm
GilaMonster - Sunday, May 7, 2017 2:44 PMWHERE Registrations.CourseId NOT IN (
SELECT Registrations.StudentIdIs that ever going to work?
That's like saying "Give me all the employees who have the same name as the company's departments"
You compare courseIDs to CourseIDs, and StudentIDs to StudentIDs. Comparing a CourseID to a StudentID as you did is completely meaningless.Right operator, but wrong column.
Oh, you are right of course. I was thinking all wrong and reading your suggestion wrong.
Here are the new one which seem to work.
Select Registrations.CourseId AS 'KursID', Course.CourseName AS 'Kursnamn', Person.FName AS 'Lärarens förnamn', Person.SName AS 'Efternamn', person.StudentId
From Registrations
INNER JOIN
Course on
Course.CourseId = Registrations.CourseId
Inner Join
Teacher on
Course.TeacherId = Teacher.TeacherId
Inner Join
Person on
Teacher.TeacherId = Person.TeacherId
WHERE Registrations.CourseId NOT IN (
SELECT Registrations.CourseId
FROM Registrations
Where Registrations.StudentId = 3
)
May 9, 2017 at 7:50 am
Did you read the part about posting DDL at the front of this forum? We have to make all kinds of guesses about keys, constraints, references, etc. Another piece of advice is not to rename columns for display purposes; if the data element is called "course_id" in the schema and data dictionary, then this name should not change or be translated in the database; this is why we have presentation layers. The purpose of an alias is to name a newly created or computed column, not for display. Furthermore, a table models a set of things, so it's a name is always a collective noun or plural name. What you've told us is you have only one student. That's probably not right.
WITH Unregistered_Courses(student_id, course_id)
(SELECT '0003' AS student_id, course_id FROM Courses AS C
EXCEPT
SELECT '0003' AS student_id, course_id
FROM Registations AS R
WHERE student_id = '0003')
SELECT U.student_id, U.course_id, C.course_name
FROM Unregistered_Courses AS U,
Courses AS C
WHERE U.course_id = C.course_id;
SQL has had standard set operations for years now. You can take all courses offered in the catalog and subtract the courses for which your particular student has registered. After that you can use the course_id to find the name of the course.
Please post DDL and follow ANSI/ISO standards when asking for help.
May 9, 2017 at 9:07 am
larsp777se - Thursday, May 4, 2017 3:27 PM
Did you read the part about posting DDL at the front of this forum? We have to make all kinds of guesses about keys, constraints, references, etc. Another piece of advice is not to rename columns for display purposes; if the data element is called "course_id" in the schema and data dictionary, then this name should not change or be translated in the database; this is why we have presentation layers. The purpose of an alias is to name a newly created or computed column, not for display. Furthermore, a table models a set of things, so it's a name is always a collective noun or plural name. What you've told us is you have only one student. That's probably not right.
WITH Unregistered_Courses(student_id, course_id)
(SELECT '0003' AS student_id, course_id FROM Courses AS C
EXCEPT
SELECT '0003' AS student_id, course_id
FROM Registations AS R
WHERE student_id = '0003')
SELECT U.student_id, U.course_id, C.course_name
FROM Unregistered_Courses AS U,
Courses AS C
WHERE U.course_id = C.course_id;
SQL has had standard set operations for years now. You can take all courses offered in the catalog and subtract the courses for which your particular student has registered. After that you can use the course_id to find the name of the course.
larsp777se - Thursday, May 4, 2017 3:27 PM
Did you read the part about posting DDL at the front of this forum? We have to make all kinds of guesses about keys, constraints, references, etc. Another piece of advice is not to rename columns for display purposes; if the data element is called "course_id" in the schema and data dictionary, then this name should not change or be translated in the database; this is why we have presentation layers. The purpose of an alias is to name a newly created or computed column, not for display. Furthermore, a table models a set of things, so it's a name is always a collective noun or plural name. What you've told us is you have only one student. That's probably not right.
WITH Unregistered_Courses(student_id, course_id)
(SELECT '0003' AS student_id, course_id FROM Courses AS C
EXCEPT
SELECT '0003' AS student_id, course_id
FROM Registations AS R
WHERE student_id = '0003')
SELECT U.student_id, U.course_id, C.course_name
FROM Unregistered_Courses AS U,
Courses AS C
WHERE U.course_id = C.course_id;
SQL has had standard set operations for years now. You can take all courses offered in the catalog and subtract the courses for which your particular student has registered. After that you can use the course_id to find the name of the course.
No, I didn't read the part about DDL and apologize for that. Was thinking about if and how I should post the tables.
So, again I apologize and thanks for the info! And, no. I have not only one student.
May 9, 2017 at 2:21 pm
larsp777se - Monday, May 8, 2017 11:51 PMGilaMonster - Sunday, May 7, 2017 2:44 PMWHERE Registrations.CourseId NOT IN (
SELECT Registrations.StudentIdIs that ever going to work?
That's like saying "Give me all the employees who have the same name as the company's departments"
You compare courseIDs to CourseIDs, and StudentIDs to StudentIDs. Comparing a CourseID to a StudentID as you did is completely meaningless.Right operator, but wrong column.
Oh, you are right of course. I was thinking all wrong and reading your suggestion wrong.
Here are the new one which seem to work.
Yup, that should work fine.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply