Solved

Mysql - SQL fun

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

Are You Using the Best Web Development Editor?

The worlds of web hosting and web development are constantly evolving. Every year we see design trends change, coding standards adapt and new frameworks/CMS created. With such a quick pace of change it’s easy to get lost trying to keep up.

See if your editor made the list.

Question has a verified solution.

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

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 …
This article discusses how to implement server side field validation and display customized error messages to the client.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

631 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