April 29, 2010 at 4:24 am
I'm sure that this is a simple question but I'm having trouble getting any results.
I have 3 tables
Members - contains member details
Quizzes - contains quiz details
MembersQuizzes - lookup table plus some supplementary data
I need to be able to write a query to return a list of members that have not taken a specified quiz
Table definitions;
CREATE TABLE [dbo].[Members](
[FirstName] [nvarchar](255) NULL,
[LastName] [nvarchar](255) NULL,
[Email] [nvarchar](255) NULL,
[MemberID] [int] IDENTITY(1,1) NOT NULL,
...... other column defs ....
CONSTRAINT [PK_Members_1] PRIMARY KEY CLUSTERED
CREATE TABLE [dbo].[Quizzes](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Title] [varchar](50) NOT NULL,
[IntroText] [varchar](max) NULL,
[Notify] [varchar](50) NULL,
[NoQuestions] [int] NOT NULL,
[AcknowledgeText] [varchar](max) NULL,
[Status] [tinyint] NOT NULL,
CONSTRAINT [PK_Quizzes] PRIMARY KEY CLUSTERED
CREATE TABLE [dbo].[MembersQuizzes](
[MemberId] [int] NOT NULL,
[QuizId] [int] NOT NULL,
[Score] [int] NOT NULL
CONSTRAINT [PK_MembersQuizzes] PRIMARY KEY CLUSTERED
example table data from MembersQuizzes:
MemberIdQuizIdScore
3112
323
6111
10111
1026
1037
18111
The MembersQuizzes table MemeberId nd QuizId have FK constraints to the respective tables
The query I am have tried without success is (always returns an empty dataset):
SELECT Members.FirstName, Members.LastName
FROM Members JOIN MembersQuizzes ON MembersQuizzes.MemberId = Members.MemberID
WHERE NOT EXISTS (SELECT *
FROM MembersQuizzes
WHERE MembersQuizzes.QuizId = @QuizId)
ORDER BY Members.LastName, Members.FirstName
If @QuizId = 3 then I would expect members with memberId 3, 6, 18 to be returned
If I replace the NOT EXISTS with NOT IN then I get a syntax error.
All help and guidance gratefully received.
April 29, 2010 at 4:34 am
http://sqlinthewild.co.za/index.php/2010/02/18/not-exists-vs-not-in/
Does this work? (Untested as I didn't feel like converting the raw data you posted into insert statements)
SELECT Members.FirstName, Members.LastName
FROM Members
WHERE NOT EXISTS (SELECT 1
FROM MembersQuizzes
WHERE MembersQuizzes.MemberId = Members.MemberID -- link to outer resultset
AND MembersQuizzes.QuizId = @QuizId
)
ORDER BY Members.LastName, Members.FirstName
Exists just checks for the existence of a record in the subquery. It has to be correlated (refer to the outer query), otherwise the subquery will return only one set of results for the entire outer resultset and you'll either get all rows back or none.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 29, 2010 at 4:38 am
The equivalent with IN would be
SELECT Members.FirstName, Members.LastName
FROM Members
WHERE Members.MemberID NOT IN (SELECT MembersQuizzes.MemberId
FROM MembersQuizzes
WHERE MembersQuizzes.QuizId = @QuizId
)
ORDER BY Members.LastName, Members.FirstName
This post may also be of interest, as it goes a bit more into the differences between the IN and Exists syntax
http://sqlinthewild.co.za/index.php/2009/08/17/exists-vs-in/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 29, 2010 at 4:49 am
Fantastic! That works perfectly, thank you for your quick response.
I also have a requirement to list all the members that have not taken all quizzes and list the quiz titles that they haven't taken. I'm not sure that is achievable in a single SQL query and will probably have to be done in the programming code.
So a result set might look like:
John Smith - Quiz 1, Quiz 3, Quiz 5
John Doe - Quiz 1
Jane Doe - Quiz 4, Quiz 5
Is this possible as a single query?
TIA Hunnie
April 29, 2010 at 4:58 am
I would recommend that you write the SQL to get a standard resultset and do the pivoting for display in the front end.
So your SQL query, for the example results, returns
John Smith Quiz 1
John Smith Quiz 3
John Smith Quiz 5
John Doe Quiz 1
Jane Doe Quiz 4
Jane Doe Quiz 5
While it is possible to create comma-delimited lists in SQL, it's probably not the best place to do it. It's a display problem, and that's what front end apps are good at.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 29, 2010 at 5:05 am
Oh, and the query... This is one way, it may be a little slow if there's lots of data. Idea is to get all possible combinations of member and quiz, then remove the ones that the person has taken.
SELECT Members.FirstName, Members.LastName, Quizzes.Title
FROM Members
CROSS JOIN Quizzes
WHERE NOT EXISTS (SELECT 1
FROM MembersQuizzes
WHERE Members.MemberID = MembersQuizzes.MemberID
AND Quizzes.ID = MembersQuizzes.QuizID
)
Not a particularly nice query, should get what you want though.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 29, 2010 at 5:14 am
You are a god - thank you.
The query works perfectly (again) and I can do all the necessary presentation/reporting via the front-end code.
Many thanks again for your quick response
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply