从另一个表到第一个表格连接一系列项目

Question

我的表格定义为。

survey_question_answers
    id  |  surveyId | questionId | title | description | creationDate
survey_questions
        id  |  surveyId | title | description | creationDate

我正在使用mySQL查询

SELECT `SurveyQuestion`.*, `Answer`.* FROM `uaehub`.`survey_questions` AS `SurveyQuestion` LEFT JOIN `uaehub`.`survey_question_answers` AS `Answer` ON (`SurveyQuestion`.`id` = `Answer`.`questionId`)  WHERE `SurveyQuestion`.`id` = 1  GROUP BY SurveyQuestion.id

问题是，它只是获取1个答案，而不是所有答案。我如何修改查询以获取我的SurveyQuession

附加的所有答案

我试图在cakephp

中这样做

$questions = $this->SurveyQuestion->find('all', array(   
                'conditions' => array('SurveyQuestion.id' => $survey['id']),
                'fields' => array(
                        'SurveyQuestion.*',
                        'Answer.*'
                ),
                'joins' => array(
                        array(
                                'type' => 'LEFT',
                                'table' => 'survey_question_answers',
                                'alias' => 'Answer',
                                'conditions' => array('SurveyQuestion.id = Answer.questionId')
                        ),                                                  
                ),
                'group' => array(
                        'SurveyQuestion'
                ),                
        ));

这就是我想要的

{
    "SurveyQuestion": {
      "id": "1",
      "surveyId": "1",
      "title": "Did you exercised early in the morning?",
      "description": "If Yes, please choose Yes. If no please choose NO",
      "creationDate": "2016-12-02 09:39:18"
    },
    "Answer": [{
      "id": "1",
      "surveyId": "1",
      "questionId": "1",
      "answer": "Yes",
      "creationDate": "2016-12-02 09:39:44"
    },
{
      "id": "2",
      "surveyId": "1",
      "questionId": "1",
      "answer": "NO",
      "creationDate": "2016-12-02 09:39:44"
    }]
  }

这样的东西，有一系列答案

我得到的ATM是

{
    "SurveyQuestion": {
      "id": "1",
      "surveyId": "1",
      "title": "Did you exercised early in the morning?",
      "description": "If Yes, please choose Yes. If no please choose NO",
      "creationDate": "2016-12-02 09:39:18"
    },
    "Answer": {
      "id": "1",
      "surveyId": "1",
      "questionId": "1",
      "answer": "Yes",
      "creationDate": "2016-12-02 09:39:44"
    }
  }

注意，这是Cakephp的格式

Answer 1

为什么要使用组？这就是您仅获得一行的原因。忽略小组，它应该起作用。

组由WILL将结果分组，以便（在您的情况下）对于任何SurveyQuestion.ID（=每个问题）仅返回一行。