View unanswered posts | View active topics It is currently Wed Nov 29, 2023 6:46 pm



Reply to topic  [ 3 posts ] 
 multiple rows and one row 
Author Message
Junior Member

Joined: Sun Feb 08, 2009 11:58 am
Posts: 7
Post 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.


Tue Sep 08, 2009 6:10 am
Report this post
Profile
Junior Member

Joined: Sun Feb 08, 2009 11:58 am
Posts: 7
Post 
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?

Please help.
Thanks in advance for yours reply.


BELOW TABLE - structure
1. TABLE epochs: epoch, date_, refer_to_picture, title_picture, author_picture;
2. TABLE questions: epoch_id, question, date_;
3. TABLE answers: question_id, answer, boolean_type, date_;


Thu Sep 10, 2009 4:30 pm
Report this post
Profile
Display posts from previous:  Sort by  
Reply to topic   [ 3 posts ] 

Who is online

Users browsing this forum: No registered users and 0 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to:  
cron
© Copyright 2003-2008 www.php-editors.com. The ultimate PHP Editor and PHP IDE site.