help with a query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

help with a query

Author

Topic

kubiczek
Starting Member

6 Posts

Posted - 2010-11-15 : 04:06:11

Hi, I need a small help with one my query.

I have a query:


SELECT     People.Name, People.Surname, Course.CourseName, Studyplan.year
FROM         Studyplan INNER JOIN
                      Course ON Studyplan.coursecode= course.code INNER JOIN
                      People ON Studyplan.studentLogin = People.Login
WHERE     (Lide.login = 'xxx') and StudijniPlan.rok = '2009'

This query returns the name of the student and the list of courses that this student have in the year 2009.
Now I would like to have a list of remaining courses that this student do not have in the year 2009.

I tried something like that:


SELECT     Course.*
FROM         Course
WHERE NOT Exists (SELECT  Course.*
FROM         Studyplan INNER JOIN
                      Course ON Studyplan.coursecode= course.code INNER JOIN
                      People ON Studyplan.studentLogin = People.Login
WHERE     (People.login = 'xxx') and Studyplan.year= '2009')

But it´s not working. Could anybody help me to fix it please? Thank you

kubiczek
Starting Member

6 Posts

Posted - 2010-11-15 : 04:09:34

It´s not working in the sense the it returns 0 rows although it should return some rows.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-15 : 04:37:33

Your outer query has no relation to the query inside the NOT EXISTS(), that will be the problem.
Try this:


SELECT     c1.*
FROM         Course AS c1
WHERE NOT Exists (SELECT  c2.*
FROM         Studyplan INNER JOIN
                      Course AS c2 ON Studyplan.coursecode= c2.code INNER JOIN
                      People ON Studyplan.studentLogin = People.Login
WHERE     (People.login = 'xxx') and Studyplan.year= '2009' AND c1.code = c2.code)

No, you're never too old to Yak'n'Roll if you're too young to die.

kubiczek
Starting Member

6 Posts

Posted - 2010-11-15 : 04:41:11

It´s working now :-) Thanks a lot.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-15 : 05:08:06

welcome