Inverse Selects?

1. Junior Member
Join Date
Dec 1969
Posts
8

## Inverse Selects?

Hey All,<BR> <BR>I&#039m having trouble figuring this one out. I have two tables: Quizzes and StudentQuizzes. Quizzes are, well quizzes and Student Quizzes represent that a specific student has taken a specific quiz. Here are the tables:<BR> <BR>QUIZZES: QUIZID, NAME, DESCRIPTION, . . . <BR> <BR>STUDENTQUIZZES: STUDENTQUIZID, QUIZID, USERID, TIMESTARTED, TIMECOMPETED<BR> <BR>(QUIZZES.QUIZID & STUDENTQUIZZES.STUDENTQUIZID are primary keys. STUDENTQUIZZES.QUIZID & STUDENTQUIZZES.USERID are foreign keys.)<BR> <BR>SELECTING all the quizzes that students have taken is easy:<BR> <BR>SELECT QUIZZES.QUIZID <BR>FROM QUIZZES, STUDENTQUIZZES<BR>WHERE QUIZZES.QUIZID = STUDENTQUIZZES.QUIZID <BR> <BR>What if I wanted to get all of the quizzes that students have not yet started? That is all rows in QUIZZES that don&#039t have a corresponding entry in STUDENTQUIZZES.<BR> <BR>Any bright ideas? :)<BR> <BR>-adam

## RE: Inverse Selects?

First of all, you should have three tables.<BR><BR>1. Students<BR>2. Quizes<BR>3. Results<BR><BR>The Results table represents a student taking a quiz and their performance in that quiz. Taking this design into consideration you could use:<BR><BR>SELECT * FROM Quizes WHERE QuizID NOT IN (SELECT QuizID FROM Results WHERE StudentID=3)<BR><BR>You might consider creating this as a stored procedure that uses temporary tables for speed purposes since the NOT IN operator cannot use indexes.

3. Junior Member
Join Date
Dec 1969
Posts
8

## RE: Inverse Selects?

Actually there are seperate results tables -- one for each type of question. And there is a seperate Students table called Users. I think you&#039ll see that if you read the original post more carefully.<BR><BR>But your response helps me -- thanks a lot!<BR><BR>-adam

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•