Avatar of Steve Tinsley
Steve Tinsley
Flag for United Kingdom of Great Britain and Northern Ireland asked on

mysql query

I am trying to create a database to hold a voting system.
I am beginning to getting the database up and running simply but an struggling on the query.

The tables are like this:

QUESTIONS
questionID
question

QUESTIONOPTIONS
optionID
questionID
option
correct

QUESTIONSUBMISSION
submissionID
questionID
optionID



From this I want to create a query (or view) to show the entries, like below:

submissionID     question                option
       001              Colour of sky           Blue
       002              Colour of Sky           Red
       003              10 Plus 5                   15
       004              Colour of Sky           Blue
       006              10 Plus 5                   15
etc........

Eventually I will use php to create a graph for each questions.

If someone can help with the query it will get me started!

Many Thank

Steve
Microsoft AccessMySQL ServerSQL

Avatar of undefined
Last Comment
Cornelia Yoder

8/22/2022 - Mon
mbizup

Try this:

SELECT submissionID, question, option
FROM (QUESTIONSUBMISSION  s JOIN QUESTIONS q ON s.questionID = q.QuestionID)  JOIN QUESTIONOPTIONS o ON s.questionID =o.questionID AND  s.OptionID = o.OptionID

Open in new window

Cornelia Yoder

SELECT * FROM
QUESTIONS as Q,
QUESTIONOPTIONS as O,
QUESTIONSUBMISSION as S
WHERE
Q.questionID = O.questionID AND
Q.questionID = S.questionID
ORDER BY S.submissionID, S.questionID, S.optionID
Steve Tinsley

ASKER
I assume these are 2 ways to achieve a similar thing??

Yodercm... Your query works but doesn't quite show it right (see query.png attachment)

I would expect it to show only 2 records as there has only been 2 votes.
What should i change to view this?

Thanks
query.png
questionOptions.png
questions.png
questionSubmissions.png
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
mbizup

->> I assume these are 2 ways to achieve a similar thing??

Yes.  You can use a JOIN clause or a WHERE clause to relate the data between your tables.
Steve Tinsley

ASKER
I see.
mbizup... I couldnt get your JOIN version working. It was giving me syntax errors.
Any other pointers?
Cornelia Yoder

Yes, but JOIN is so difficult and miserable to use that I avoid it when there is a much easier way.

What is the change that you want in the results?  Where  correct  is 1?

You can add any clauses to the WHERE part to get exactly what you want.  For example if you want to show only those rows where correct=1, then


SELECT * FROM
QUESTIONS as Q,
QUESTIONOPTIONS as O,
QUESTIONSUBMISSION as S
WHERE
Q.questionID = O.questionID AND
Q.questionID = S.questionID  AND
O.correct = 1
ORDER BY S.submissionID, S.questionID, S.optionID
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
mbizup

What are the exact errors?

SELECT submissionID, question, option
FROM (QUESTIONSUBMISSION  s INNER JOIN QUESTIONS q ON s.questionID = q.QuestionID)  INNER JOIN QUESTIONOPTIONS o ON s.questionID =o.questionID AND  s.OptionID = o.OptionID

Open in new window

ASKER CERTIFIED SOLUTION
mbizup

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Steve Tinsley

ASKER
Perfect mbizup! Removing the parentheses worked!

yodercm.... Id quite like to get it working your way to.... Attached is the screenprint of how I would like it to be displayed...

It shows the 4 entries.
Screen-Shot-2012-07-12-at-17.21..png
mbizup

I think the WHERE clause in her query needs to link the OptionIDs in addition to the QuestionIDs.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Cornelia Yoder

Is this what you want?

SELECT S.submissionID, Q.question, O.option
FROM
QUESTIONS as Q,
QUESTIONOPTIONS as O,
QUESTIONSUBMISSION as S
WHERE
Q.questionID = O.questionID AND
Q.questionID = S.questionID  AND
O.optionID = S.optionID AND
O.correct = 1
ORDER BY S.submissionID