Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Mysql - SQL fun

Posted on 2010-09-03
5
Medium Priority
?
464 Views
Last Modified: 2012-05-10
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
0
Comment
Question by:Canders_12
  • 3
5 Comments
 
LVL 8

Expert Comment

by:nahime
ID: 33594797
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

0
 
LVL 8

Accepted Solution

by:
nahime earned 2000 total points
ID: 33594810
Sorry I forgot the limit of only one random question
SELECT * FROM questiontable WHERE memberID <> 1000 AND questionID NOT IN (
SELECT questionID FROM answertable WHERE memberID = 1000
) ORDER BY RAND() LIMIT 1

Open in new window

0
 

Author Closing Comment

by:Canders_12
ID: 33594838
Oh my word :)

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

Expert Comment

by:ganesh_gns
ID: 33594849
Hi check the below qry...

0
 
LVL 8

Expert Comment

by:nahime
ID: 33594865
No problem Canders_12, sometimes the best solution is also the simplest one :D
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
The viewer will learn how to dynamically set the form action using jQuery.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question