CODEWITHSUNDEEP
asp.netsqlsql-server-2008
Hammam Muhareb
Hammam Muhareb

Reputation: 355

SQL statement to select from two tables with where

I have two tables defined as follow:

Courses

course ID     course Name
1             Math
2             Science
3             geography

and another table (its use is to show enrolled students in courses):

student_courses

course ID     student ID      Final cost
1             2               100
2             3               200

I need a SQL statement that selects only the courses that a certain student (its value is a parameter from a control on my page -but that is not my problem) isn't enrolled in....

My latest try is this:

SELECT 
   Courses.[Course ID], Courses.[Course name] 
FROM 
   Courses, student_courses 
where 
   Courses.[Course ID] <> student_courses.[Course ID]

Upvotes: 0

Views: 1282

Answers (3)

user1588622
user1588622

Reputation:

The simplest (Transact) SQL I can think of is this:

select 
     [courses].[course ID]
   , [courses].[course name]
from [courses]
left outer join [student_courses]  
       on   [student_courses].[course ID]  = [courses].[course ID] 
       and  [student_courses].[student ID] = @studentID
where 
     [student_courses].[student ID] is null

Don't put the "[student_courses].[student ID] = @studentID" in the where clause because this will cause the null values you're interested in to be thrown away and your left join will look like an inner join.

If you want to get unnecessarily complicated you could use group, having and count instead.

select 
    [courses].[course ID]
  , [courses].[course name]
from [courses]
left outer join [student_courses]  
           on   [student_courses].[course ID]  = [courses].[course ID] 
           and  [student_courses].[student ID] = @studentID
group by
     [courses].[course ID]
   , [courses].[course name]
having count([student_courses].[course ID]) = 0

It's probably a good idea to lose the spaces from column names. It'll only cause you trouble in the long term.

Upvotes: 1

alex
alex

Reputation: 3742

SELECT [Course ID], [Course name] 
FROM Courses
WHERE [Course ID] NOT IN 
 (SELECT Course ID from student_courses WHERE [student ID]=1)

Upvotes: 3

marc_s
marc_s

Reputation: 754368

You just need something like this:

SELECT
    c.[Course ID], c.[Course Name]
FROM
    dbo.Course c
WHERE
    NOT EXISTS (SELECT * FROM dbo.StudentCourses sc
                WHERE [Student ID] = 2 AND [Course ID] = c.[Course ID])

See my SQL Fiddle showing this.

Basically, just select those courses that do not have an entry in Student_Courses with that [Student ID]

Upvotes: 4

Related Questions