CODEWITHSUNDEEP
sql-server-2005
Gopi
Gopi

Reputation: 541

TSQL Finding Incomplete courses

From the StudentDetails Table

StudentDetails

SID   Name   CourseCompleted

1    Andrew   CS001
1    Andrew   CS002
1    Andrew   CS003

2    Grey     CS001 
2    Grey     CS005 
2    Grey     CS002

3    Jon      CS002   
3    Jon      CS005 
3    Jon      CS008

How to generate the folowing output ( Course not completed by each student)

SID   Name   Course Not Completed

1    Andrew   CS005
1    Andrew   CS008

2    Grey     CS003 
2    Grey     CS008 

3    Jon      CS001   
3    Jon      CS003

Upvotes: 1

Views: 74

Answers (3)

Martin Smith
Martin Smith

Reputation: 453287

With StudentDetails As
(
SELECT 1 SID,   'Andrew' Name,  'CS001' CourseCompleted union all
SELECT 1,    'Andrew',   'CS002' union all
SELECT 1 ,   'Andrew' ,  'CS003' union all
SELECT 2 ,   'Grey'    , 'CS001' union all
SELECT 2 ,   'Grey' ,    'CS005' union all
SELECT 2 ,   'Grey' ,    'CS002' union all
SELECT 3 ,   'Jon'  ,    'CS002' union all
SELECT 3 ,   'Jon'  ,    'CS005' union all
SELECT 3 ,   'Jon'  ,    'CS008' 
),
Courses AS
(
SELECT DISTINCT CourseCompleted AS Course
FROM StudentDetails
),
Students As
(
SELECT DISTINCT SID, Name
FROM StudentDetails
)

SELECT s.SID, s.name, c.Course AS [Course not Completed] FROM Students s
CROSS JOIN Courses c 
EXCEPT
SELECT SID, name, CourseCompleted
FROM StudentDetails
ORDER BY s.SID, c.Course

Upvotes: 0

Blorgbeard
Blorgbeard

Reputation: 103467

select s.SID, s.Name, c.Course as [Course Not Completed]
from (select distinct CourseCompleted [Course] from StudentDetails) c,
StudentDetails s
where not exists (
    select * from StudentDetails where SID=s.SID and CourseCompleted=c.Course
)

Of course, if you have a table listing all possible courses, you could replace the subquery in the from clause with that table.

Upvotes: 1

Fosco
Fosco

Reputation: 38526

select distinct a.SID, a.Name, b.CourseCompleted as `Course Not Completed`
from StudentDetails a,
(select distinct CourseCompleted from StudentDetails) b
where not exists
(select 1 from StudentDetails where SID = a.SID and CourseCompleted = b.CourseCompleted)
order by a.SID

Upvotes: 1

Related Questions