Confusion about "not in ... " - 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

General SQL Server Forums

New to SQL Server Programming

Confusion about "not in ... "

Author

Topic

EugeneLim11
Posting Yak Master

167 Posts

Posted - 2008-12-22 : 01:54:06

Hi all,

I want to select all studentID, Modcode, Grp, DateAbsent from a table where the values are not in another table. How do I do that?

e.g.
Select StudentID, ModCode, Grp, DateAbsent from AttendanceTable
where StudentId , ModCode, Grp, DateAbsent not in (Select StudentId, Module, Term , Date from WarningLetterAbsences)

The above query results in error

Msg 4145, Level 15, State 1, Line 2
An expression of non-boolean type specified in a context where a condition is expected, near ','.

ModCode correspond to Module, Grp correspond to Term, DateAbsent Corresponds to Date.

Help is very much appreciated. Thank you.

Regards,

Eugene

check out my blog at http://www.aquariumlore.blogspot.com

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-12-22 : 02:02:27

Select StudentID, ModCode, Grp, DateAbsent from AttendanceTable
where ModCode not in (Select Module from WarningLetterAbsences)
AND grp NOT IN (select term from WarningLetterAbsences)
AND DateAbsent NOT IN (select Date from WarningLetterAbsences)

Try like this
U should compare one field when not in is used then it will compare the result (as boolean type)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-12-22 : 07:13:49

For completeness I would like to say that there is no relationship to a special StudentID in the example above.

Webfred

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

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-12-22 : 07:23:30

may be i think studentid is a foreign key for WarningLetterAbsences with references of AttendanceTable

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-22 : 07:55:10

Do not use the suggestion posted by bklr. It is completely wrong.

SELECT		at.StudentID,
		at.ModCode,
		at.Grp,
		at.DateAbsent
FROM		AttendanceTable AS at
LEFT JOIN	WarningLetterAbsences AS wla ON wla.StudentId = at.StudentId 
			AND wla.Module = at.ModCode
			AND wla.Term = at.Grp
			AND wla.Date = at.DateAbsent
WHERE		wla.StudentId IS NULL

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources