Data being repeated when using INNER JOIN on Oracle

Asked

Viewed 949 times

1

I have three tables, which are the following : ALUNO,CLASSE and MATRICULA.

PUPIL

 NR_RGM
 NM_NOME
 NM_PAI
 NM_MAE
 DT_NASCIMENTO
 ID_SEXO

CLASS

CD_CLASSE
NR_ANOLETIVO
CD_ESCOLA
CD_GRAU
NR_SERIE
TURMA
CD_PERIODO

LICENSE PLATE

CD_CLASSE
NR_RGM
DT_MATRICULA

I’m making a INNER JOIN with the three tables to return me a query. This is the query :

select a.nm_nome
from aluno a
inner join matricula ma on (ma.nr_rgm = a.nr_rgm)
inner join classe c on (c.cd_classe = ma.cd_classe)
where a.nm_nome LIKE '%SILAS%' AND c.cd_classe = ma.cd_classe

The query works, but the problem is that it returns me repeated results. And appears in Oracle by the following results :

How can I do just returns me the required data without being repeated ? I know there are already some other similar questions, but they didn’t help me in what I need.

oracle join

  • If you only want to bring 1 record of each, make a grouping by PK, example group by a.nr_rgm , the reason to bring several lines is that some table has more than 1 record referring to the student/matricula

  • Yes I managed to solve here now through GROUP BY in which I used the name instead of the primary key and it worked out, thank you very much. If you want, you can put as an answer and I’ll give you the right answer.

1 answer

3


You need to group the data to return 1 record of each.

group by example:

select a.nm_nome
  from aluno a
 inner join matricula ma on (ma.nr_rgm = a.nr_rgm)
 inner join classe c on (c.cd_classe = ma.cd_classe)
 where a.nm_nome LIKE '%SILAS%' AND c.cd_classe = ma.cd_classe
 group by a.nm_nome

Another solution is to use distinct

select distinct a.nm_nome
  from aluno a
 inner join matricula ma on (ma.nr_rgm = a.nr_rgm)
 inner join classe c on (c.cd_classe = ma.cd_classe)
 where a.nm_nome LIKE '%SILAS%' AND c.cd_classe = ma.cd_classe

Related: DISTINCT and GROUP BY, what is the difference between the two statements?

You need to analyze the following: Will I have 2 students with the same name? If yes, grouping only by name would not be ideal, you will need to put the a.nr_rgm in your SELECT to separate both.

select a.nr_rgm, a.nm_nome
  from aluno a
 inner join matricula ma on (ma.nr_rgm = a.nr_rgm)
 inner join classe c on (c.cd_classe = ma.cd_classe)
 where a.nm_nome LIKE '%SILAS%' AND c.cd_classe = ma.cd_classe
 group by a.nr_rgm, a.nm_nome

Browser other questions tagged oracle join

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