Query: How to get both Question and multi Answers in one query

Question

For this question, Question and Answer are just my example tables for one-many relationship. For example: Question(QuestionID, QuestionTitle, QuestionContent) and Answer(AnswerID, AnswerContent, QuestionID)

In one query, I want to get one question and all answers of this question by QuestionID:

1. How to write a query like that

2. In case that, I "think" will have each rows is an answer with additional fields of question. This will make duplication because all those answers have same question content --> cost memory to store additional data. So compare to this: get QuestionID and query get Question. get QuestionID and query to get all Answers. Does this way lost performance than first approach but gain more memory ?

Here is my script:

DROP TABLE IF EXISTS `Question` ;

CREATE TABLE IF NOT EXISTS `Question` (
  `QuestionID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `QuestionContent` VARCHAR(200) NOT NULL,
  PRIMARY KEY (`QuestionID`),
ENGINE = InnoDB;


DROP TABLE IF EXISTS `Answer` ;

CREATE TABLE IF NOT EXISTS `Answer` (
  `AnswerID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `QuestionID` INT UNSIGNED NOT NULL,
  `ReplyContent` VARCHAR(200) NOT NULL,
  PRIMARY KEY (`AnswerID`),
  INDEX `fk_Answer_Question1_idx` (`QuestionID` ASC),
  CONSTRAINT `fk_Answer_Question1`
    FOREIGN KEY (`QuestionID`)
    REFERENCES `Question` (`QuestionID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
ENGINE = InnoDB;

Thanks :)

Answer 1

You could make it with a simple JOIN, the question is: Are you going to have others tables?. That could make that you have to redo your design. I suggest EAV if you're going to use more tables with some configuration between Questions and Answers.

With your actual design the query would be like:

mysql> SELECT 
    -> q.QuestionID,
    -> q.QuestionContent,
    ->     a.AnswerID,
    ->     a.ReplyContent
    -> FROM test.Question AS q
    -> JOIN test.Answer AS a ON (a.QuestionID=q.QuestionID)
    -> WHERE q.QuestionID=1;
+------------+------------------------------+----------+--------------+
| QuestionID | QuestionContent              | AnswerID | ReplyContent |
+------------+------------------------------+----------+--------------+
|          1 | How many indexes may I have? |        1 | Use 6        |
|          1 | How many indexes may I have? |        2 | Use 1        |
|          1 | How many indexes may I have? |        3 | A or B       |
+------------+------------------------------+----------+--------------+
3 rows in set (0.00 sec)

mysql> EXPLAIN SELECT 
    -> q.QuestionID,
    -> q.QuestionContent,
    ->     a.AnswerID,
    ->     a.ReplyContent
    -> FROM test.Question AS q
    -> JOIN test.Answer AS a ON (a.QuestionID=q.QuestionID)
    -> WHERE q.QuestionID=1;
+----+-------------+-------+-------+-------------------------+---------+---------+-------+------+-------------+
| id | select_type | table | type  | possible_keys           | key     | key_len | ref   | rows | Extra       |
+----+-------------+-------+-------+-------------------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | q     | const | PRIMARY                 | PRIMARY | 4       | const |    1 |             |
|  1 | SIMPLE      | a     | ALL   | fk_Answer_Question1_idx | NULL    | NULL    | NULL  |    3 | Using where |
+----+-------------+-------+-------+-------------------------+---------+---------+-------+------+-------------+
2 rows in set (0.00 sec)

mysql> 

Related:

• To EAV or not to EAV?
• Efficient query for an advanced EAV model
• Eav MySQL Search

Answer 2

Just borrowing the code from oNare's answer, I have a suggesion

If you want to show the Question just once and all the Answers, there is a fancy trick you can do by aggregating the answers into a single field using the CONCAT and GROUP_CONCAT functions

SET group_concat_max_len = 1024 * 1024 * 1024;
SELECT 
    q.QuestionID,
    q.QuestionContent,
    GROUP_CONCAT(CONCAT(a.AnswerID,':',a.ReplyContent) ORDER BY a.AnswerID) AllTheAnswers
FROM test.Question AS q
JOIN test.Answer AS a ON (a.QuestionID=q.QuestionID)
WHERE q.QuestionID=1
GROUP BY q.QuestionID;