Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Mysql - SQL fun

Posted on 2010-09-03
5
Medium Priority
?
461 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 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

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

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

In this article, we’ll look at how to deploy ProxySQL.
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

722 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