Hi everyone!
The tables given below shows that every rows in the 'questions' table is assigned to four rows in the 'answers' table. I wonder how to select randomly four questions and sixteen answers and then display them separately. I want to gain the following effect:
first site:
1 question - 1 answer
- 1 answer
- 1 answer
- 1 answer
second site:
3 question - 3 answer
- 3 answer
- 3 answer
- 3 answer
third site:
2 question - 2 answer
- 2 answer
- 2 answer
- 2 answer
fourth site:
4 question - 4 answer
- 4 answer
- 4 answer
- 4 answer
If I write this query:
SELECT question FROM questions WHERE epoch_id = 2 AND id = 4;
then the result is:
question number 4
If I write this query:
SELECT answer FROM questions JOIN answers ON questions.epoch_id = 2 AND questions.id=answers.question_id AND answers.question_id = 4;
then the result is four times:
answer number 4
answer number 4
answer number 4
answer number 4
What do I have to do if I want to gain more rows from the 'answers' table?
CREATE TABLE epochs (id INT AUTO_INCREMENT NOT NULL,
epoch VARCHAR(100),
date_ TIMESTAMP NOT NULL,
refer_to_picture TEXT,
title_picture TEXT,
author_picture TEXT,
PRIMARY KEY (id)) ENGINE=InnoDB;
CREATE TABLE questions (id INT AUTO_INCREMENT NOT NULL,
epoch_id INT,
question TEXT,
date_ TIMESTAMP NOT NULL,
PRIMARY KEY (id),
INDEX (epoch_id),
FOREIGN KEY (epoch_id)
REFERENCES epochs(id)
ON DELETE RESTRICT
ON UPDATE CASCADE) ENGINE=InnoDB;
CREATE TABLE answers (id INT AUTO_INCREMENT NOT NULL,
question_id INT,
answer TEXT,
boolean_type BOOL,
date_ TIMESTAMP NOT NULL,
PRIMARY KEY (id),
INDEX (question_id),
FOREIGN KEY (question_id)
REFERENCES questions(id)
ON DELETE RESTRICT
ON UPDATE CASCADE) ENGINE=InnoDB;
Please help.
Thanks in advance for yours reply.
