SQL Help for selecting available quizes

Last Post 29 May 2007 03:12 PM by SQLUSA. 1 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
dantindall
New Member
New Member

--
23 May 2007 03:23 AM
Hello,
The following tables exist
QuizTable
QuizID (primary key)
Title
Description
IsAvailable

UserQuizTable
UserQuizID (PK)
QuizID (FK)
DateTimeComplete
Score
UserName

I need to develop a stored procedure that will return all of the quizes from the QuizTable that are available and that a particular user has not yet taken.


To return a list of quizes with no entries at all in the UserQuiz table, I'm using the following SQL:
select Quiz.QuizID, Quiz.Title, Quiz.Description,
Quiz.IsAvailable FROM Quiz Left Outer Join
UserQuiz ON Quiz.QuizID = UserQuiz.QuizID
Where UserQuiz.QuizID is null
AND Quiz.IsAvailable=0

This is only getting me 1/2 the way to where I need to end up.

Any suggestions greatly appreciated.
SQLUSA
New Member
New Member

--
29 May 2007 03:12 PM
You have to do a LEFT JOIN (or RIGHT JOIN) between the (all) quiz table and the taken table.
You are not authorized to post a reply.

Acceptable Use Policy