Doubts LEFT JOIN SQL

Question

Doubts LEFT JOIN SQL

Navigation

#1 by (2 votes)
#2 by (1 votes)

2

I need to do a query, where I enter a person code and sql returns all the people who are inside that group who is the person I typed the code to.

Example: I enter code '1', The code refers to the person code, this person is in a 'A' class, but in the 'A' group there are more people, I need to get all the people in this 'A' class. There is a possibility that the person has more than one TURMA, so I need to get all people from ALL the TURKS OF THAT CODE I reported.

So far you can make a mess, but it's coming back 2 lines as null and brings the rest right.

select Q5.* from
( select * FROM tbl_TURMA) Q1
left join
( select * from tbl_PESSOA_TURMA WHERE COD_IDENT_PESSO = '38'  ) Q2
on Q1.COD_IDENT_TURMA = Q2.COD_IDENT_TURMA
left join
( select * from tbl_PESSOAS ) Q3
on Q2.COD_IDENT_PESSO = Q3.COD_IDENT_PESSO
left join
(select * from tbl_PESSOA_TURMA) Q4
on Q4.COD_IDENT_TURMA = Q2.COD_IDENT_TURMA
left join
(select * from tbl_PESSOAS) Q5
on Q4.COD_IDENT_PESSO = Q5.COD_IDENT_PESSO

TURMAS | ALUNOS | TURMAS_ALUNOS
1      | 1      | 1 - 1
2      | 2      | 1 - 2
3      | 3      | 2 - 1 
       | 4      | 2 - 5
       | 5      | 1 - 6
       | 6      | 3 - 6

A minha SQL com o CÓDIGO de ALUNO 1 neste caso deveria retornar:
ALUNOS 1,2,6,5
Pois o aluno 1 está na turma 1, e juntamente com ele está o aluno 2 e aluno 6.
Porem o aluno 1 também está na turma 2, e juntamente com ele o aluno 5

sql

asked by anonymous 09.09.2015 / 15:02

2 answers

1

Renan, As a comment, putting * even if you are picking up all the fields in question of performance is not the best option. See if the code below resolves and do not tell me what happened, please.

SELECT Q5.* FROM tbl_TURMA AS Q1
LEFT JOIN tbl_PESSOA_TURMA AS Q2
ON Q1.COD_IDENT_TURMA = Q2.COD_IDENT_TURMA
LEFT JOIN tbl_PESSOAS AS Q3
ON Q2.COD_IDENT_PESSO = Q3.COD_IDENT_PESSO
WHERE Q2.COD_IDENT_PESSO = '38'

SELECT Q3.* FROM tbl_TURMA AS Q1
LEFT JOIN tbl_PESSOA_TURMA AS Q2
ON Q1.COD_IDENT_TURMA = Q2.COD_IDENT_TURMA
LEFT JOIN tbl_PESSOAS AS Q3
ON Q2.COD_IDENT_PESSO = Q3.COD_IDENT_PESSO
WHERE   Q2.COD_IDENT_PESSO = '38'
Q3.COD_IDENT_PESSO IN (SELECT COD_IDENT_PESSO FROM tbl_PESSOA_TURMA WHERE COD_IDENT_TURMA = Q2.COD_IDENT_TURMA)

09.09.2015 / 15:24

Save Checkboxes Values in Android Studio Write to two different tables using the same View

score 2 · Accepted Answer

Try to use the following query, it will return all people who participate in the group of the person entered in the condition.

SELECT DISTINCT
    PESSOAS.* 
FROM 
    TBL_PESSOAS PESSOAS
    LEFT JOIN TBL_PESSOA_TURMA AS PT ON PT.COD_IDENT_PESSO = PESSOAS.COD_IDENT_PESSO
    LEFT JOIN TBL_TURMA AS TURMA ON TURMA.COD_IDENT_TURMA = PT.COD_IDENT_TURMA
WHERE   
    TURMA.COD_IDENT_TURMA IN (
                            SELECT 
                                DISTINCT(PT.COD_IDENT_TURMA)
                            FROM 
                                TBL_PESSOA_TURMA PT
                                LEFT JOIN TBL_PESSOAS AS PESSOAS ON PESSOAS.COD_IDENT_PESSO = PT.COD_IDENT_PESSO
                            WHERE 
                                PESSOAS.COD_IDENT_PESSO = '38'
                           )