Need help with Exists/NotExists

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 2000 Forums

SQL Server Development (2000)

Need help with Exists/NotExists

Author

Topic

MrRay
Starting Member

28 Posts

Posted - 2005-04-07 : 16:14:56

SAMPLE DATA:

  SESSIONS table

     SessionID    TotalScore
     1001            80%

  ANSWERS table

     AnswerID     SessionID       Question# Points
     9001           1001              1             1
     9002           1001              2             1
     9003           1001              4             1
     9004           1001              5             1

I have written a learning management system through which our users take online tests.

Every time a user starts a test it creates a record in SESSIONS with a unique SessionID.

Each answer in that test is recorded in ANSWERS with a unique AnswerID, but it also stores the SessionID for that session.

What I want to do is execute an SQL statement that will find record in SESSIONS for which there is no record in ANSWERS with a particular question number. (Say we wanted to know who skipped a particular question, for example.)

So say John takes a test and it records questions 1, 2, 4, 5. I want to find John's SessionID by asking for the SessionID of any record in ANSWERS where there is no QuestionNumber equal to 3.

I've tried all combinations of INNER JOINS, EXISTS and NOT EXISTS with no luck, and I think my brain is too exhausted right now to view the solution in the right perspective.

Help!

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2005-04-07 : 16:38:30

Does this work for you??


SELECT dbo.Sessions.Session ID,
       dbo.Sessions.TotalScore
FROM   dbo.Sessions
WHERE  NOT EXISTS(SELECT * FROM dbo.Answers WHERE dbo.Answers.SessionID = dbo.Sessions.SessionID AND dbo.Answers.Question = 3)

Dustin Michaels

MrRay
Starting Member

28 Posts

Posted - 2005-04-08 : 10:10:14

quote:

Originally posted by DustinMichaels

Does this work for you??


SELECT dbo.Sessions.Session ID,
       dbo.Sessions.TotalScore
FROM   dbo.Sessions
WHERE  NOT EXISTS(SELECT * FROM dbo.Answers WHERE dbo.Answers.SessionID = dbo.Sessions.SessionID AND dbo.Answers.Question = 3)

Dustin Michaels

It worked famously! Now that I see your code it makes perfect sense, but I couldn't get my head around it last night.

Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources