Doubt LEFT JOIN SQL

Asked

Viewed 88 times

2

I need to do a query, where I inform a person code and sql returns me all the people who are within that group that is the person to whom I typed the code.

Example: I enter code '1', The code refers to the person’s code, this person is in a class 'A', but in class 'A' there are more people, I need to get all the people in this class 'A'. There is the possibility of the person having more than one CLASS, then I need to take ALL PEOPLE OF ALL CLASSES OF THAT CODE I informed.

Until now manage to make a mess, but is returning 2 lines as null and brings the rest correct.

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

  • Don’t you want me to repeat? Then which class will appear to the person who is in two classes?

2 answers

2


Try using the following query, 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'
                           )

  • You gave it all right Thank you very much.

1

Renan, As a comment, putting * even if you are picking all the fields in the performance issue is not the best option. See if the code below resolves and if you do not report 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'

Try this by placing a subselect, in which case you will select all the code of person who has the same class as the boy.

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)

  • Henrique’s query is only returning to the class the person is in. I need you to show me all the PEOPLE in the class that this "STUDENT" IS, to better understand I need this student’s class friends, just to illustrate.

  • And I’m reviewing and I see that will return only the person. Mas to understand. Table tbl_TURMA ( You have class information) tbl_PESSOAS ( You have people information) and table tbl_PESSOA_TURMA ( and a relationship table n:m that has the relationship between the right person and class?

  • Right. That’s exactly it

  • Is showing the student the amount of times he appears in a class, for example I am have two classes, my name and my data repeat 2 times.

  • Use the GROUP BY tabela.campoturma see the field class and see if it works. If you do not inform me that we have found another solution.

  • How would sql look ?

  • It is still returning only the replicated value.

  • 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)
GROUP BY Q2.COD_IDENT_PESSO

  • It’s not working yet. Try to explain better, I am teacher RENAN, but I am also a student, I need to know all students, who are from the room where I am a teacher, and the students in the room where I am a student, however we must take care because there may be students who are also in these two classes.

  • The sql you made, is only returning the group of classes to which I typed the code, I need to return the students of these classes.

  • Wow I put him to group by the code of the person not in the class. Take a test by putting it to group by class code, not giving I’ll think of another way not to repeat. Q2.COD_IDENT_TURMA I believe changing the initial grouping we made to GROUP BY Q3.COD_IDENT_PESSO detail ai was table tries ai picking up the code of the person from table Q3

Show 6 more comments

Browser other questions tagged sql

You are not signed in. Login or sign up in order to post.