asked on

Mysql - SQL fun

Hello all,

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

nahime

If questiontable is the first table, answertable is the second and 1000 is the user id you can do a query like this:

SELECT * FROM questiontable WHERE memberID <> 1000 AND questionID NOT IN (
SELECT questionID FROM answertable WHERE memberID = 1000
)

Open in new window

ASKER CERTIFIED SOLUTION

nahime

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Canders_12

ASKER

Oh my word :)

that is simple yet genius, many many thanks. This was giving me a bit of a headache lol

ganesh_gns

Hi check the below qry...

nahime

No problem Canders_12, sometimes the best solution is also the simplest one :D