SQL Available Matters for a Career

Question

SQL Available Matters for a Career

Navigation

#1 by (0 votes)

0

Good evening friends, I have days killing me with an SQL that I do not know how to do, I tell you I am developing a system for the management of universities, and I am trapping in the list of available subjects. I have these tables:

CREATE TABLE EST_CARRERAS (
    CCARRERA              CODIGO NOT NULL /* CODIGO = INTEGER DEFAULT 0 NOT NULL */,
    CODIGO                DESCRIPCION_CORTA DEFAULT 'NONE' NOT NULL /* DESCRIPCION_CORTA = VARCHAR(50) */,
    DESCRIPCION           DESCRIPCION_MEDIA NOT NULL /* DESCRIPCION_MEDIA = VARCHAR(120) */,
    ESTATUS               LOGICO DEFAULT 1 NOT NULL /* LOGICO = INTEGER DEFAULT 1 NOT NULL */
);

CREATE TABLE EST_MATERIAS (
    CMATERIA              CODIGO NOT NULL /* CODIGO = INTEGER DEFAULT 0 NOT NULL */,
    CODIGO                DESCRIPCION_CORTA DEFAULT 'NONE' /* DESCRIPCION_CORTA = VARCHAR(50) */,
    DESCRIPCION           DESCRIPCION_MEDIA NOT NULL /* DESCRIPCION_MEDIA = VARCHAR(120) */
);

CREATE TABLE EST_MAT_CAR (
    CMATE                 CODIGO NOT NULL /* CODIGO = INTEGER DEFAULT 0 NOT NULL */,
    CMATERIA              CODIGO NOT NULL /* CODIGO = INTEGER DEFAULT 0 NOT NULL */,
    CCARRERA              CODIGO NOT NULL /* CODIGO = INTEGER DEFAULT 0 NOT NULL */,
    SEMESTRE              INTEGER DEFAULT '0' NOT NULL
);

CREATE TABLE EST_MAT_PRES (
    CPRESLA               CODIGO /* CODIGO = INTEGER DEFAULT 0 NOT NULL */,
    CMATERIA              CODIGO /* CODIGO = INTEGER DEFAULT 0 NOT NULL */,
    CPRESLACION           CODIGO /* CODIGO = INTEGER DEFAULT 0 NOT NULL */,
    CCARRERA              CODIGO /* CODIGO = INTEGER DEFAULT 0 NOT NULL */,
    CTIPO                 CODIGO /* CODIGO = INTEGER DEFAULT 0 NOT NULL */,
    CANT                  MONTOS /* MONTOS = DOUBLE PRECISION DEFAULT 0.0 */
);

CREATE TABLE EST_INSCRIPCION (
    CINSCRIPCION          CODIGO NOT NULL /* CODIGO = INTEGER DEFAULT 0 NOT NULL */,
    CCARRERA              CODIGO NOT NULL /* CODIGO = INTEGER DEFAULT 0 NOT NULL */,
    CPERIODO              CODIGO NOT NULL /* CODIGO = INTEGER DEFAULT 0 NOT NULL */,
    CALUMNO               CODIGO NOT NULL /* CODIGO = INTEGER DEFAULT 0 NOT NULL */,
    FINSCRIPCION          FECHA NOT NULL /* FECHA = DATE */
);

CREATE TABLE EST_INSCRIPCION_DET (
    CINSCRIPCION_DET      CODIGO NOT NULL /* CODIGO = INTEGER DEFAULT 0 NOT NULL */,
    CINSCRIPCION          CODIGO NOT NULL /* CODIGO = INTEGER DEFAULT 0 NOT NULL */,
    CMATE                 CODIGO NOT NULL /* CODIGO = INTEGER DEFAULT 0 NOT NULL */,
    CSECCION              CODIGO NOT NULL /* CODIGO = INTEGER DEFAULT 0 NOT NULL */,
    NOTA                  MONTOS /* MONTOS = DOUBLE PRECISION DEFAULT 0.0 */
);

Then the first table manages the races, the 2nd the subjects, the 3rd the relation matter - career (cmate) the 4th table the priority of the subjects (cmateria: matter to see or evaluate, cpreslacion: matter that had to be approved to see the cmateria, ctype = if it is 2 CANT that are the number of UC approved), the next 2 are the header and the details of the inscriptions, then I made a small example where to an enrollment record (est_encripcion ) I inserted 3 lines (where I repeated 1 subject 2 times) to verify that the selection brings me the available subjects of a career in question:

SELECT
MC.CMATE,
M.CMATERIA,
M.DESCRIPCION,
C.CCARRERA,
C.DESCRIPCION
FROM EST_MAT_CAR MC
INNER JOIN EST_MATERIAS M ON MC.CMATERIA=M.CMATERIA
INNER JOIN EST_CARRERAS C ON MC.CCARRERA=C.CCARRERA
LEFT JOIN (SELECT 
DISTINCT(ID.CMATE)
FROM
EST_INSCRIPCION_DET ID
INNER JOIN EST_INSCRIPCION I ON ID.CINSCRIPCION=I.CINSCRIPCION
WHERE ID.NOTA > 10
AND I.CALUMNO=1
AND I.CCARRERA=1) ID2 ON ID2.CMATE=MC.CMATE
WHERE ID2.CMATE IS NULL
AND C.CCARRERA=1

The select brings me all the subjects that the student can see, but it is bringing me a subject that I should be able to see since it "clashes" with one that has not been approved, (in this case est_mat_pres intervenes) but I do not know how to do it said select to bring me the available subjects and if one of the subjects appears in the field cpreslacion, do not show me the field in the list.

I do not know if they understand me, but I'm very involved and I can not get the answer. The system I am doing in PHP and I presume that I will have to do a kind of LOOP, but I was looking for the most effective way to do it through a SELECT.

sql php firebird

asked by JuJoGuAl 25.09.2017 в 01:38

source

1 answer

Using AJAX with CRUD How to apply different styles to items in a ListView?

score 0 · Accepted Answer

Together with my project partner we managed to create a procedure that does the job of listing the available subjects:

CREATE OR ALTER PROCEDURE ALUM_MATERIAS_PENDIENTES(
    P_ALUMNO TYPE OF CODIGO,
    P_CARRERA TYPE OF CODIGO)
RETURNS(
    MAT_COD TYPE OF DESCRIPCION_CORTA,
    MAT_CODE TYPE OF CODIGO,
    MAT_NAME TYPE OF DESCRIPCION_CORTA,
    COD_CARRERA TYPE OF CODIGO,
    DESCRIP_CARRERA TYPE OF DESCRIPCION_CORTA,
    MAT_SEM INTEGER)
AS
DECLARE VARIABLE V_CCARRERA TYPE OF CODIGO;
DECLARE VARIABLE V_CMATERIA TYPE OF CODIGO;
DECLARE VARIABLE V_PRELACION INTEGER;
DECLARE VARIABLE V_CTIPO INTEGER;
DECLARE VARIABLE V_CANT INTEGER;
DECLARE VARIABLE V_UC INTEGER;
BEGIN
  /* DESCRIPCION: DEVUELVE LAS MATERIAS PENDIENTES POR APROBAR, VALIDANDO LAS PRELACIONES
  */

  IF (P_ALUMNO IS NULL OR P_CARRERA IS NULL) THEN
  EXIT;

  /*UN CICLO POR CADA UNA DE LAS MATERIAS QUE PERTENECEN A LA CARRERA NO APROBADAS*/
  FOR SELECT
            M.CODIGO,
            M.CMATERIA,
            M.DESCRIPCION,
            C.CCARRERA,
            C.DESCRIPCION,
            MC.SEMESTRE
       FROM EST_MAT_CAR MC
       JOIN EST_MATERIAS M ON MC.CMATERIA=M.CMATERIA
       JOIN EST_CARRERAS C ON MC.CCARRERA=C.CCARRERA
  LEFT JOIN (SELECT DISTINCT (ID.CMATE)
               FROM EST_INSCRIPCION_DET ID
               JOIN EST_INSCRIPCION I ON (ID.CINSCRIPCION = I.CINSCRIPCION)
              WHERE ID.NOTA >= 10
                AND I.CALUMNO = :P_ALUMNO
                AND I.CCARRERA = :P_CARRERA) ID2 ON (ID2.CMATE = MC.CMATE)
       WHERE ID2.CMATE IS NULL
         AND C.CCARRERA = :P_CARRERA
               INTO :MAT_COD,
                    :MAT_CODE,
                    :MAT_NAME,
                    :COD_CARRERA,
                    :DESCRIP_CARRERA,
                    :MAT_SEM

        DO BEGIN

        /*VALIDO SI LA MATERIA TIENE ALGUN TIPO DE PRELACION*/
        IF (EXISTS (SELECT P.CMATERIA
                      FROM EST_MAT_PRES P
                     WHERE P.CMATERIA = :MAT_CODE AND P.CCARRERA = :COD_CARRERA)) THEN

                     /*BUSCO CON QUIEN TIENE PRELACION*/
                 FOR SELECT P.CPRESLACION,
                            P.CCARRERA,
                            P.CTIPO,
                            P.CANT
                       FROM EST_MAT_PRES P
                      WHERE P.CMATERIA = :MAT_CODE
                        AND P.CCARRERA = :COD_CARRERA
                       INTO :V_CMATERIA,
                            :V_CCARRERA,
                            :V_CTIPO,
                            :V_CANT

                DO BEGIN

                IF (V_CTIPO = 1) THEN
                BEGIN
                     /*BUSCO SI ESA MATERIA YA FUE APROBADA*/
                     SELECT COUNT(*)
                       FROM EST_INSCRIPCION EI
                       JOIN EST_INSCRIPCION_DET EID ON (EID.CINSCRIPCION = EI.CINSCRIPCION)
                       JOIN EST_MAT_CAR EMC ON (EMC.CMATE = EID.CMATE)
                       JOIN EST_CARRERAS EC ON (EC.CCARRERA = EMC.CCARRERA)
                       JOIN EST_MATERIAS EM ON (EM.CMATERIA = EMC.CMATERIA)
                      WHERE EID.NOTA >= 10
                        AND EMC.CMATERIA = :V_CMATERIA
                        AND EMC.CCARRERA = :V_CCARRERA
                        INTO :V_PRELACION;

                        IF (V_PRELACION > 0 AND NOT EXISTS (SELECT P.CMATERIA
                                                              FROM EST_MAT_PRES P
                                                             WHERE P.CMATERIA = :MAT_CODE
                                                               AND P.CCARRERA = :COD_CARRERA
                                                               AND P.CTIPO = 2)) THEN
                        SUSPEND;
                END

                IF (V_CTIPO = 2) THEN
                BEGIN

                     SELECT COALESCE(SUM(EM.UC),'0') AS UC
                       FROM EST_INSCRIPCION EI
                       JOIN EST_INSCRIPCION_DET EID ON (EID.CINSCRIPCION = EI.CINSCRIPCION)
                       JOIN EST_MAT_CAR EMC ON (EMC.CMATE = EID.CMATE)
                       JOIN EST_CARRERAS EC ON (EC.CCARRERA = EMC.CCARRERA)
                       JOIN EST_MATERIAS EM ON (EM.CMATERIA = EMC.CMATERIA)
                      WHERE EID.NOTA >= 10
                        AND EI.CALUMNO = :P_ALUMNO
                       INTO :V_UC;

                       IF (V_UC >= V_CANT) THEN
                       SUSPEND;

                END
                END

         ELSE

         SUSPEND;
        END
END

Which receives the student and the career, if the student has taken a career before and has not passed the subject returns, otherwise returns the ones you can see.

:)