Randomly generate id from database and make sure it isn't repeated

Question

I'm trying to randomly generate questions to be answered in a quiz from my question table in a database but I don't want 2 of the same questions in a quiz.

As the question is asked I am putting them into a table using

SqlCommand cmdInsert = new SqlCommand("INSERT INTO [dbo].[Tests]( TestID, UserID, Date, QuestionsID) VALUES ('"+1+"','"+ StudentLoginForm.UserInformation.UserID + "',GETDATE(),'" + QuestionID.Questionid + "')", con);

with the aim to compare the contents of this table with the questions id to pick a question.

I am using the following select statement to select the questions to be asked but it some times produces the same question

 SqlCommand cmd = new SqlCommand("SELECT QuestionID, Answer, Question, OptionA, OptionB, OptionC, OptionD FROM dbo.Questions LEFT JOIN Tests ON Questions.QuestionID = Tests.QuestionsID order by newid()", con);

Any help to try and solve this problem would be much appreciated .

Answer 1

Your join is likely producing duplicates, so you'll have to either fix the data, or use a DISTINCT in your query.

SELECT DISTINCT QuestionID, Answer, Question, OptionA, OptionB, OptionC, OptionD
FROM dbo.Questions 
LEFT JOIN Tests ON Questions.QuestionID = Tests.QuestionsID