Data being repeated when using INNER JOIN in Oracle

Question

Data being repeated when using INNER JOIN in Oracle

Navigation

#1 by (2 votes)

1

I have three tables, which are as follows: ALUNO , CLASSE and MATRICULA .

STUDENT

 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

REGISTRATION

CD_CLASSE
NR_RGM
DT_MATRICULA

I'm doing INNER JOIN with all three tables so I can return a query. Being this 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 it appears in Oracle by the following results:

How can I do it only returns me the required data without being repeated? I know there are some other similar questions already, but they did not help me what I needed.

join oracle

asked by anonymous 16.11.2017 / 17:06

1 answer

How to read a string with C ++ space inside a function Use two code :: blocks at the same time?

score 2 · Accepted Answer

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's the difference between the two statements?

You need to analyze the following: Will I have 2 students with the same name? If so, grouping by name only would not be ideal, you will need to put 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