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
8/22/2022 - Mon
mbizup
Try this:
SELECT submissionID, question, optionFROM (QUESTIONSUBMISSION s JOIN QUESTIONS q ON s.questionID = q.QuestionID) JOIN QUESTIONOPTIONS o ON s.questionID =o.questionID AND s.OptionID = o.OptionID
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?
->> 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
SELECT submissionID, question, optionFROM (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
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