Select two tables with different columns in ORACLE

Question

Select two tables with different columns in ORACLE

Navigation

#1 by (2 votes)

1

I have three tables, one call ALUNO , one call MATRICULA , and another call CLASSE , and I need to select all students who are male and belong to the same class as a student who has a RGM specific.

The tables are:

STUDENT Table

  CREATE TABLE ALUNO(
    NR_RGM NUMBER(8),
    NM_NOME VARCHAR2(40) CONSTRAINT NOME_ALUNO_NN NOT NULL,
    NM_PAI VARCHAR2(40) CONSTRAINT PAI_ALUNO_NN NOT NULL,
    NM_MAE VARCHAR2(40) CONSTRAINT MAE_ALUNO_NN NOT NULL,
    DT_NASCIMENTO DATE CONSTRAINT DT_NASC_ALUNO_NN NOT NULL,
    ID_SEXO VARCHAR2(1) CONSTRAINT SEXO_ALUNO_NN NOT NULL,
    CONSTRAINT ALUNO_NR_RGM_PK PRIMARY KEY (NR_RGM));

REGISTRATION Table:

CREATE TABLE MATRICULA(
CD_CLASSE NUMBER(8),
NR_RGM NUMBER(8),
DT_MATRICULA DATE CONSTRAINT DATA_MATRICULA_NN NOT NULL,
CONSTRAINT MATRICULA__CD_CLASSE_NR_RGM_PK PRIMARY KEY (CD_CLASSE,NR_RGM),
CONSTRAINT MATRICULA_CD_RGM_FK FOREIGN KEY(NR_RGM) REFERENCES ALUNO(NR_RGM),
CONSTRAINT MATRICULA_CD_CLASSE_FK FOREIGN KEY(CD_CLASSE) 
    REFERENCES CLASSE(CD_CLASSE));

CLASS Table:

CREATE TABLE CLASSE(
CD_CLASSE NUMBER(8),
NR_ANOLETIVO NUMBER(4) CONSTRAINT ANO_LETIVO_CLASSE_NN NOT NULL,
CD_ESCOLA NUMBER(6),
CD_GRAU NUMBER(2),
NR_SERIE NUMBER(2) CONSTRAINT SERIE_CLASSE_NN NOT NULL,
TURMA VARCHAR2(2) CONSTRAINT TURMA_CLASSE_NN NOT NULL,
CD_PERIODO NUMBER(2),
CONSTRAINT CLASSE_PK PRIMARY KEY (CD_CLASSE),
CONSTRAINT GOIABAO FOREIGN KEY(CD_ESCOLA) 
      REFERENCES ESCOLA(CD_ESCOLA),
CONSTRAINT CLASSE_CD_CLASSE_FK FOREIGN KEY(CD_GRAU) REFERENCES GRAU(CD_GRAU),
CONSTRAINT CLASSE_CD_PERIODO_FK FOREIGN KEY(CD_PERIODO) REFERENCES PERIODO(CD_PERIODO),
CONSTRAINT CLASSE_ANO_LETIVO_CK CHECK(NR_ANOLETIVO > 2000));

How could I do this?

For example, to select students from the same student room as a specific RGM would look like this:

SELECT * FROM Matricula WHERE cd_classe = 
(SELECT cd_classe FROM matricula WHERE nr_rgm = '12150');

But how could I do this with two different columns from two different tables?

oracle select

asked by anonymous 14.11.2017 / 01:28

1 answer

how to put monetary mask for EditText in an Android application? How to find the key name in a dictionary by the value contained in it?

score 2 · Answer 1

From what I understand, MATRICULA.nr_rgm reference ALUNO.nr_rgm and MATRICULA.cd_classe reference CLASSE.cd_classe

Then to return all students of the same class who have the same NR_RGM and are male, would do something like this:

select a.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.nr_rgm = 12150 and a.id_sexo = 'M';

I did not test, because I do not have the tables created here, see if this helps in any way.