Solved

Mysql - SQL fun

Posted on 2010-09-03
5
447 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Google Dork query 7 62
Echo'd values in dropdowns 6 28
PHP Underscores vs. Directory separators 5 26
How do I fix this error with quotation marks? 4 24
This article discusses four methods for overlaying images in a container on a web page
A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
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 dynamically set the form action using jQuery.

756 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