I need to put the SELECT below to work inside a cursor, but I am not able to join the 3 tables, can someone help me?
SELECT DISTINCT PROFESSOR.NOME, ESTUDANTE.NOME
FROM PROFESSOR INNER JOIN SUPERVISIONA ON
PROFESSOR.ID_PROF=SUPERVISIONA.ID_PROF INNER JOIN ESTUDANTE ON SUPERVISIONA.ID_EST=ESTUDANTE.ID_EST
WHERE ESTUDANTE.NOME='RITA'
Based on the little information passed, I believe that your query is already correct, the suggestion would be to put an UPPER in the STUDENT clause. NAME because you are passing the string 'RITA' but it may be that the database is 'Rita'. Another query suggestion would be:
SELECT P.NOME NOME_PROFESSOR, E.NOME NOME_ESTUDANTE
FROM PROFESSOR P, ESTUDANTE E, SUPERVISIONA S
WHERE S.ID_PROF = P.ID_PROF
AND S.ID_EST = E.ID_EST
AND UPPER(E.NOME) = 'RITA';
This takes into consideration that the SUPERVISIONA table has the PROFESSOR and STUDENT ids.
Result:
Nowanexampleofaplusingthequeryinacursor,wouldlooklikethis:
DECLAREv_nome_professorVARCHAR(30);v_nome_estudanteVARCHAR(30);CURSORC_TESTEISSELECTP.NOMENOME_PROFESSOR,E.NOMENOME_ESTUDANTEFROMPROFESSORP,ESTUDANTEE,SUPERVISIONASWHERES.ID_PROF=P.ID_PROFANDS.ID_EST=E.ID_ESTANDUPPER(E.NOME)='RITA';BEGINFORcINC_TESTELOOPBEGINv_nome_professor:=c.NOME_PROFESSOR;v_nome_estudante:=c.NOME_ESTUDANTE;DBMS_OUTPUT.PUT_LINE('Valoresretornados:');DBMS_OUTPUT.PUT_LINE('Professor:'||v_nome_professor||'/Estudante:'||v_nome_estudante);EXCEPTIONWHENNO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE('Nenhumdadoencontrado!');END;ENDLOOP;END;/Result:
