3

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 :)

  • Make a JOIN with Question.QuestionID = Answer.QuestionID?. Make a subquery with all the QuestionID you want to filter in Answer.QuestionID IN (SELECT QuestionID FROM Question WHERE QuestionID='1'); – oNare Jul 16 '15 at 19:35
  • will join more performance than query individual table? thanks :) – hqt Jul 16 '15 at 19:37
  • The performance comes having a normalized database with all its components (indexes, engines, etc). – oNare Jul 16 '15 at 19:39
  • can you tell me more, please. in case in index questionID and answerID, and I use InnoDB. thanks :) – hqt Jul 16 '15 at 19:41
  • Add in your question your CREATE TABLE scripts of Answer and Question please. – oNare Jul 16 '15 at 19:43
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;
  • I was thinking about this, but if he need the exactly AnswerID for frontend ambient and CRUDs. You could use SEPARATOR '\n' if you want AllTheAnswers vertically. – oNare Jul 16 '15 at 20:26
  • @oNare If this were for a PHP script, one could take AllTheAnswers and run PHP's explode function (php.net/manual/en/function.explode.php) using the colon(:) as the delimiter. Then, the array is open to individualize the display of each answer. – RolandoMySQLDBA Jul 16 '15 at 20:36
2

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:

  • Can you tell me about "EAV"? And can you tell me performance when I query on each table rather than using join ? thanks :) – hqt Jul 16 '15 at 20:17
  • I've added a link in the answer. You could search all over dba.SE and you will find good solution to your question. – oNare Jul 16 '15 at 20:19
  • BTW I borrowed your code for my answer, so +1 for copyright infringement. – RolandoMySQLDBA Jul 16 '15 at 20:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.