title: How to make a JOIN with table containing attribute value zero?
body: <p>I have a table that I defined one column to receive zero 0 as value. I did this because zero 0 represents all values. The table's name is <code>agendas</code> and the column it's <code>turmas_id</code>, this column has relationship with table <code>turmas</code> but <code>turmas_id</code> in <code>agendas</code> it's not a foreignkey because I can add 0 as saied before. </p>
  
  <p>The problem is when I make a JOIN using these tables because I need return all attributes with zero value and valid keys added in table <code>turmas</code>. </p>
  
  <p>I tried use LEFT JOIN and INNER JOIN but the result it's not what I wait. I can use JOIN if <code>id</code> exists in table <code>turmas</code> and table <code>agendas</code> because it's a valid foreign key but I can't return other values with 0 in <code>agendas</code> attribute <code>turmas_id</code> and this is exactly what I need.</p>
  
  <p>How could I do this ?</p>
  
  <p><strong>I need display this result</strong> </p>
  
  <pre><code>//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 
  </code></pre>
  
  <p><strong>SQL</strong></p>
  
  <pre><code>$agendamentos = $this-&gt;Agenda-&gt;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
  </code></pre>
  
  <p><strong>Model</strong></p>
  
  <p><a href="https://i.stack.imgur.com/uzeMt.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/uzeMt.png" alt="enter image description here"></a></p>
  
  <p><strong>JSON result</strong></p>
  
  <pre><code>{
    "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": "&lt;p&gt;teste 1&lt;/p&gt;\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"
        }
      }
    ]
  }
  </code></pre>