How to show the quizzes that the employee did not participate in them?
I have the following database design:
Employee Table: Username, Name Quiz Table: QuizID, Title, IsSent, Description UserQuiz Table: UserQuizID, QuizID, Score, Username
(IsSent is a flag (of a bit type) that indicates if the quiz has been sent to all employees or not)
I need now to write a query that shows the title and description of the quizzes that have been sent to the employee but he did not participate in them. So when the employee goes to his profile in the system, he will see the quizzes that he did participate in them (if any). How to do that?
I wrote a general query that shows the quizzes that all employees did not participate in them. It should be specific for a given/specified employee. So how to modify it?
SELECT Title, Description FROM dbo.Quiz WHERE (NOT EXISTS (SELECT QuizID FROM dbo.UserQuiz WHERE (dbo.Quiz.QuizID = QuizID)))
Try this way:-
SELECT Q.Title, Q.Description FROM Quiz Q WHERE (NOT EXISTS (SELECT UQ.QuizID FROM UserQuiz UQ, Employee E WHERE Q.QuizID = UQ.QuizID and E.Username = UQ.Username) )