mysql - 如何与包含属性值零的表进行联接？

Question

我有一个表，我定义了一列来接收零 0 作为值。我这样做是因为零 0 代表所有值。表的名称是agendas，列是turmas_id，该列与表turmas有关系，但turmas_id在agendas 它不是外键，因为我可以像前面所说的那样添加 0。

问题是当我使用这些表进行 JOIN 时，因为我需要返回表 turmas 中添加的所有具有零值和有效键的属性。

我尝试使用 LEFT JOIN 和 INNER JOIN 但结果不是我等待的。如果表 turmas 和表 agendas 中存在 id，我可以使用 JOIN，因为它是有效的外键，但我无法返回其他值为 0 的值在 agendas 属性 turmas_id 中，这正是我所需要的。

我怎样才能做到这一点？

我需要显示此结果

//table agendas
-----------------------------------------
  turmas_id   |   descricao
-----------------------------------------
    0         |   this attribute contain zero and it's not exists in table turmas
   16         |   table turmas contain id 16 it is a foreign key
    0         |   this attribute contain zero and it's not exists in table turmas
   23         |   table turmas contain id 23 it is a foreign key 

SQL

$agendamentos = $this->Agenda->query("SELECT * FROM responsavel_alunos RespAlunos "
                . "INNER JOIN pessoas Responsavel ON (Responsavel.id = RespAlunos.pessoas_id) "
                . "INNER JOIN pessoas Aluno ON (Aluno.id = RespAlunos.pessoas_id1) "
                . "INNER JOIN matriculas Matricula ON (Matricula.pessoas_id = Aluno.id) "
                . "RIGHT JOIN turmas Turma ON (Turma.id = Matricula.turmas_id OR Turma.id = 0) "
                . "INNER JOIN escolas Escola ON (Escola.id = Matricula.escolas_id) "
                . "INNER JOIN agendas Agenda ON (Agenda.turmas_id = Turma.id) "
                . "WHERE Responsavel.id = ? ORDER BY Agenda.created DESC "
                , array($id)); //id do responsavel

型号

JSON 结果

{
  "status": "1",
  "result": [
    {
      "RespAlunos": {
        "id": "5",
        "pessoas_id": "8",
        "pessoas_id1": "9",
        "created": "2015-09-21 10:25:46",
        "modified": "2015-09-21 10:25:46"
      },
      "Responsavel": {
        "id": "8",
        "nome": "responsavel ",
        "email": "responsavel @hotmail.com",
        "tipopessoas_id": "3",
        "status": "1",
        "created": "2015-09-21 10:17:17",
        "modified": "2015-09-21 10:17:17"
      },
      "Aluno": {
        "id": "9",
        "nome": "aluno",
        "email": "aluno@gmail.com",
        "tipopessoas_id": "1",
        "status": "1",
        "created": "2015-09-21 10:18:41",
        "modified": "2015-09-21 10:18:41"
      },
      "Matricula": {
        "id": "6",
        "referencia": "238",
        "pessoas_id": "9",
        "turmas_id": "4",
        "escolas_id": "2",
        "status": "1",
        "created": "2015-09-21 10:35:08",
        "modified": "2016-02-18 10:51:20"
      },
      "Turma": {
        "id": "4",
        "descricao": "4º ano",
        "created": "2015-09-21 10:31:32",
        "modified": "2015-09-21 10:31:32"
      },
      "Escola": {
        "id": "2",
        "descricao": "Santa Luz Unidade 2",
        "created": "2015-09-17 23:09:38",
        "modified": "2015-09-17 23:09:38"
      },
      "Agenda": {
        "id": "34",
        "data": "2016-02-29 14:40:00",
        "descricao": "<p>teste 1</p>\r\n",
        "escolas_id": "2",
        "turmas_id": "4",
        "created": "2016-02-29 14:40:21",
        "modified": "2016-02-29 14:40:21"
      }
    },
    {
      "RespAlunos": {
        "id": "5",
        "pessoas_id": "8",
        "pessoas_id1": "9",
        "created": "2015-09-21 10:25:46",
        "modified": "2015-09-21 10:25:46"
      },
      "Responsavel": {
        "id": "8",
        "nome": "responsavel ",
        "email": "responsavel @hotmail.com",
        "tipopessoas_id": "3",
        "status": "1",
        "created": "2015-09-21 10:17:17",
        "modified": "2015-09-21 10:17:17"
      },
      "Aluno": {
        "id": "9",
        "nome": "aluno",
        "email": "aluno@gmail.com",
        "tipopessoas_id": "1",
        "status": "1",
        "created": "2015-09-21 10:18:41",
        "modified": "2015-09-21 10:18:41"
      },
      "Matricula": {
        "id": "6",
        "referencia": "238",
        "pessoas_id": "9",
        "turmas_id": "4",
        "escolas_id": "2",
        "status": "1",
        "created": "2015-09-21 10:35:08",
        "modified": "2016-02-18 10:51:20"
      },
      "Turma": {
        "id": "4",
        "descricao": "4º ano",
        "created": "2015-09-21 10:31:32",
        "modified": "2015-09-21 10:31:32"
      },
      "Escola": {
        "id": "2",
        "descricao": "Santa Luz Unidade 2",
        "created": "2015-09-17 23:09:38",
        "modified": "2015-09-17 23:09:38"
      },
      "Agenda": {
        "id": "27",
        "data": "2016-02-29 08:24:00",
        "descricao": "descricao",
        "escolas_id": "2",
        "turmas_id": "4",
        "created": "2016-02-29 08:25:20",
        "modified": "2016-02-29 08:25:20"
      }
    }
  ]
}

Answer 1

尝试使用 IN ？而不是使用=？在 WHERE 子句中，因为您使用了数组变量