Help with SubQuery in SQL?

Question

Help with SubQuery in SQL?

Navigation

#1 by (3 votes)
#2 by (1 votes)
#3 by (0 votes)

0

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

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

I thought about using SubQuery to do this, but I still could not do it. What would be the best solution to do a SELECT by returning the existing classes in the Class table for example, class 15 (third year) together with the class that receives 0 all of them?

I'm trying to do this with JOIN, but in that case 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

mysql sql

asked by anonymous 01.03.2016 / 13:41

3 answers

In the automatic return of the pagseguro I can not save the information in the bank Converting division expression in C ++ to C #

score 3 · Answer 1

Do as Daniel Saraiva directed, using left join . See your query as it should be. I tested it here and it worked out.

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

score 1 · Answer 2

1

use 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

01.03.2016 / 13:42

score 0 · Answer 3

Test this sql, using the or class .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;