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
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
Last Comment
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
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
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
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
->> 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.
Yes. You can use a JOIN clause or a WHERE clause to relate the data between your tables.
ASKER
I see.
mbizup... I couldnt get your JOIN version working. It was giving me syntax errors.
Any other pointers?
mbizup... I couldnt get your JOIN version working. It was giving me syntax errors.
Any other pointers?
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
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
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
ASKER CERTIFIED SOLUTION
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.
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
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
I think the WHERE clause in her query needs to link the OptionIDs in addition to the QuestionIDs.
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
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
Open in new window