Help with Subquery in SQL?

Question

I have in my system a table called Agendas, this table is linked with other tables Class and School through the columns turmas_id and escola_id. What happens is that the column turmas_id is not a related Foreign key even getting the code from the Class table it can also receive 0, as I created 0 as a representation for sending message in the system to all the Classes of the referring school.

What happens is that now I need to display the data both for existing Class and for this that receives 0 referencing all Class School. If I use JOIN to join the tables only valid codes will be returned and the code 0 will be ignored because it does not exist in the table Class.

I thought about using Subquery to do this, but I haven’t been able to do it yet. What would be the best solution to make a SELECT returning the existing classes in the table Class for example, class 15 (third year) together with the Class that receives 0 symbolizing all ?

I am trying so with JOIN, but in this case the values in 0 are not returned.

$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) "
                    . "INNER JOIN turmas Turma ON (Turma.id = Matricula.turmas_id) "
                    . "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

EDIT

$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) "
                    . "LEFT JOIN turmas Turma ON (Turma.id = Matricula.turmas_id OR Turma.id = 0) "
                    . "INNER JOIN escolas Escola ON (Escola.id = Matricula.escolas_id) "
                    . "LEFT JOIN agendas Agenda ON (Agenda.turmas_id = Turma.id) "
                    . "WHERE Responsavel.id = ? ORDER BY Agenda.created DESC "
                    , array($id)); //id do responsavel

Image of the model

Answer 1

Do as Daniel Saraiva directed, using the left join . Check your query as it should look. I tested it here and it worked.

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)
    LEFT JOIN turmas Turma ON (Turma.id = Matricula.turmas_id)
    INNER JOIN escolas Escola ON (Escola.id = Matricula.escolas_id)
    LEFT JOIN agendas Agenda ON (Agenda.turmas_id = Turma.id)
WHERE Responsavel.id = ?
ORDER BY Agenda.created DESC

Answer 2

use the left join that will work example

select USUARIO.USU_CODIGO, USU_NOME, GRU_CODIGO from USUARIO left join USUGRUPO ON USUARIO.USU_CODIGO = USUGRUPO.USU_CODIGO

Answer 3

Test this sql using class or.id = 0;

    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) 
    INNER 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;