CODEWITHSUNDEEP
sqlsql-serverdatabase
Moza
Moza

Reputation: 3

SQL Server displaying query output in certain format

Question:

How can I display the student name, the department name and the course name of every course that the student has not taken?

I have tried using generating a cartesian product like this: 

SELECT  
    NAME, student.dept_name, title
FROM 
    student
JOIN 
    (SELECT 
         id, title
     FROM 
         takes, course
     WHERE 
         takes.course_id <> course.course_id) a ON student.id = a.id
ORDER BY 
    NAME ASC, title ASC

And it works fine as long as the student only takes 1 course, but not if he attends to more than one

Can you guys provide a solution ? Thanks in advance

Database relations

The desired output is a connection of the values in this format 

| name | dept_name | title (course name) |

Upvotes: 0

Views: 79

Answers (3)

Paul Maxwell
Paul Maxwell

Reputation: 35623

select
    s.*, c.*
from students s
cross join courses c
left join takes t on s.student_id = t.id -- naming looks weird
                 and c.course_id = t.course_id
where t.student_id IS NULL
order by
  s.name, c.title -- or similar

A cross join forms a Cartesian product that forms all combinations of student + course. Outer join that large potential set to the actual set of students enrolled to courses. Then, the un-joined rows constitute the courses that have not been taken by (each) student.

If the questions has a small "twist" and that all you need is the list of un-taken courses then:

select distinct
    c.*
from students s
cross join courses c
left join takes t on s.student_id = t.id -- naming looks weird
                 and c.course_id = t.course_id
where t.student_id IS NULL
order by
  c.title -- or similar

Will simplify the result to just the untaken courses.

Upvotes: 0

P.Salmon
P.Salmon

Reputation: 17665

The fact that there is no connection between courses an student is not an issue you need to know all the courses a student could take so given

student

Name       CourseId
---------- ----------
Zishan     1,2,3
Ellen      2,3,4

(2 row(s) affected)

Course

courseid    coursename
----------- ----------
1           java
2           C++
3           oracle
4           dot net

Courses_taken

sid                  cid
-------------------- -----------
zishan               1
zishan               2

(2 row(s) affected)

a cross join like this

select s.Name Studentname, c.courseid cid,c.coursename CourseNAme
from student s
cross join course c

Results in

Studentname cid         CourseNAme
----------- ----------- ----------
Zishan      1           java
Zishan      2           C++
Zishan      3           oracle
Zishan      4           dot net
Ellen       1           java
Ellen       2           C++
Ellen       3           oracle
Ellen       4           dot net

Joining to courses_taken

select sc.Studentname,sc.Coursename,ct.cid
from
(
select s.Name Studentname, c.courseid cid,c.coursename CourseNAme
from student s
cross join course c
) sc
left join courses_taken  ct on sc.StudentName = ct.sid and sc.cid = ct.cid

results in this

Studentname Coursename cid
----------- ---------- -----------
Zishan      java       1
Zishan      C++        2
Zishan      oracle     NULL
Zishan      dot net    NULL
Ellen       java       NULL
Ellen       C++        NULL
Ellen       oracle     NULL
Ellen       dot net    NULL

To exclude the courses taken a null where condition

select sc.Studentname,sc.Coursename,ct.cid
from
(
select s.Name Studentname, c.courseid cid,c.coursename CourseNAme
from student s
cross join course c
) sc
left join courses_taken  ct on sc.StudentName = ct.sid and sc.cid = ct.cid
where ct.cid is null

Results in

Studentname Coursename cid
----------- ---------- -----------
Zishan      oracle     NULL
Zishan      dot net    NULL
Ellen       java       NULL
Ellen       C++        NULL
Ellen       oracle     NULL
Ellen       dot net    NULL

(6 row(s) affected)

Upvotes: 1

Ab Bennett
Ab Bennett

Reputation: 1432

Do a query that gets all combos of student and courses by doing Cartesian join, then remove all the courses a student has done by doing the 2nd query below the except. This handles a student who has not done any courses yet, which is why a not exists doesn’t work.

Select s.id, c.title 
From student s cross join courses c
Except
Select t.id, c.title 
From takes t join courses c on t.course_id = c.course_id

Upvotes: 0

Related Questions