multiple rows and one row
This is my table and my problems is under the table. Please forgive me for my bad English.
Code:
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;
These are SQL query.
1. SELECT epoch, refer_to_picture, title_picture, author_picture FROM epochs WHERE id = 2\G
2. SELECT questions.id, questions.question FROM epochs JOIN questions ON epochs.id = questions.epoch_id AND epochs.id = 2 ORDER BY RAND() LIMIT 1\G
How to refer to 'id' in table 2? This third SQL query returns all 'answer' rows. But I must write WHERE clause which indicates 'id' column in table 2. Good, but the value of 'id' isn't constant it would be variable, but how to do it?
3. SELECT answer, boolean_type FROM questions JOIN answers ON questions.id=answers.question_id WHERE answers.question_id = 2 ORDER BY RAND()\G
Additional considerations.
The problems starts when I combine 'questions' table with 'answers' table.
This SQL query shows what I want, but it returns one 'answer' row. How to combine 'question' row with 'answer' rows?
4. SELECT question, answer FROM (epochs JOIN questions ON epochs.id = questions.epoch_id AND epochs.id = 2) JOIN answers ON questions.id=answers.question_id ORDER BY RAND() limit 1\G
This SQL query shows all 'answer' rows and every time repeats 'question' row.
5. SELECT question, answer FROM (epochs JOIN questions ON epochs.id = questions.epoch_id AND epochs.id = 2) JOIN answers ON questions.id=answers.question_id limit 4\G
This SQL query doing the same thing.
SELECT questions.question, answers.answer, answers.boolean_type FROM epochs, questions, answers WHERE epochs.id = 2 AND questions.id=answers.question_id limit 4\G
Please help me. Thanks in advance for yours reply.
