Solved

Mysql - SQL fun

Posted on 2010-09-03
5
422 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 500 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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
The viewer will learn how to count occurrences of each item in an array.

777 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