Mysql - SQL fun
Posted on 2010-09-03
I am building a quiz database and am keen to ensure that a person is never asked a question that they upload and also that they never get asked a question more than once. Below is some background information.
I have a table as follows:
questionID (autonumber PK)
memberID (number - unique to each member)
question (question to ask the individual e.g. What is my name?)
correctAnswer (answer to question e.g. Colin)
My first step is to obtain all the questions that have not been uploaded by the user which I find easy enough to do. I also have the following table:
memberAnswerID (autonumber pk)
memberID (the unique member number id FK)
questionID (the unique question id FK)
memberAnswer (memners answer e.g. Alan)
How can I create the query where I select ONE question randomly that was not uploaded by the person answering it and that they have not already answered before. I hope the question makes sense?
Many thanks in advance, Colin