Data being repeated when using INNER JOIN in Oracle



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

    _

     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
    

    MATRICULUM

    CD_CLASSE
    NR_RGM
    DT_MATRICULA
    

    I'm doing a INNER JOIN with the 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 the following results:

    How can I do just returns the necessary data without being repeated? I know there are a few other similar questions, but they didn't help me with what I need.



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

    https://www.w3schools.com/sql/sql_groupby.asp 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 https://www.w3schools.com/sql/sql_distinct.asp

    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: https://pt.stackoverflow.com/q/228294/57801

    You need to analyze the following: Am I gonna have two 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
    



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2