CODEWITHSUNDEEP
mysqlsql
dziks
dziks

Reputation: 45

SQL problem with JOIN statement (multiple results from 1 table)

So I have a SQL query that selects quiz_questions and quiz_question_options But when I run my SQL query I only get 1 result from quiz_option (in my database I have 3 options for each question). How would I be able to display all 3 options for every question?

Tables:

quiz_question

-id (PK) -quiz_id(FK) -question

quiz_question_option

`-id (PK)
-quiz_question_id(FK)
-quiz_option`

This is my code so far:

SELECT quiz_question.question,quiz_question_option.quiz_option FROM quiz_question_option LEFT JOIN quiz_question ON quiz_question.id = quiz_question_option.quiz_question_id ORDER BY RAND() LIMIT 5

The limit will change depending on user input

EDIT: The result i get is:

question |quiz_option 1.question|1.answer 2.question|2.answer

What I need to get is:

question |quiz_option 1.question|1.answer 1.question|2.asnwer 1.question|3.answer 2.question|1.answer 2.question|2.answer 3.question|3.answer etc...

2.EDIT: I need to make a sql query for my project (website) so the user will select how many questions he wants to have. On the next page it would display the questions. I already have the code to display random questions. But I don't know how to display the options for the selected question.

Upvotes: 1

Views: 61

Answers (2)

Guga Rukhadze
Guga Rukhadze

Reputation: 60

I believe INNER join would help you. Try this: 

SELECT quiz_question.question, quiz_question_option.quiz_option
FROM quiz_question_option AS O
INNER JOIN quiz_question AS Q
ON Q.id= O.quiz_question_id

Upvotes: 0

Ed Bangga
Ed Bangga

Reputation: 13006

seems you want to ignore the relationship. what you want to achieve is something like cross join

select t1.question, t2.quiz_option
from quiz_question_option t1
cross join quiz_question t2

Upvotes: 1

Related Questions