0

I've this tables in my database, and I'm trying to select all the career info, including the specializations it has.

CREATE OR REPLACE TABLE School_Catalog (
    school_id INT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL UNIQUE,
    creation_datetime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) DEFAULT CHARACTER SET utf8mb4;

CREATE OR REPLACE TABLE Career_Master(
    career_id INT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT,
    school_id INT UNSIGNED NOT NULL,
    area_id TINYINT UNSIGNED NOT NULL,
    name VARCHAR(64) NOT NULL,
    FOREIGN KEY (school_id) REFERENCES School_Catalog(school_id) ON UPDATE CASCADE ON DELETE RESTRICT,
    FOREIGN KEY (area_id) REFERENCES Area_Catalog(area_id) ON UPDATE CASCADE ON DELETE RESTRICT,
    INDEX(area_id)
) DEFAULT CHARACTER SET utf8mb4;

CREATE OR REPLACE TABLE Specialization_Detail
(
    specialization_id INT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT,
    career_id INT UNSIGNED NOT NULL,
    name VARCHAR(127) NOT NULL,
    FOREIGN KEY (career_id) REFERENCES Career_Master(career_id) ON UPDATE CASCADE ON DELETE RESTRICT,
    INDEX(name)
) DEFAULT CHARACTER SET utf8mb4;

For achieving that I have this procedure:

DELIMITER //
CREATE OR REPLACE PROCEDURE UThesis.getSchoolCareers(
    IN school_id_in INT UNSIGNED
)
BEGIN
    DECLARE careers CURSOR FOR
    SELECT
        CM.career_id AS id,
        CM.name AS career,
        AC.name AS area
    FROM Career_Master CM
    LEFT JOIN Area_Catalog AC on AC.area_id = CM.area_id
    WHERE school_id = school_id_in;

    FOR row IN careers DO
        SELECT * from specialization_detail WHERE career_id = row.id;
    END FOR;
END //
DELIMITER ;

But It only returns the last select, I remember once I read how to do multiple selects inside a procedure and was somthing like Select((Select ...) AS s1, (Select ...) AS s2) but now I've no idea, Is there any way or I should do query by query?

1 Answer 1

0

That's not the correct syntax for using a cursor. But why even use a cursor when JOIN will do the job much faster. Or a GROUP_CONCAT.

BEGIN
    SELECT
        CM.career_id AS id,
        CM.name AS career,
        AC.name AS area,
        GROUP_CONCAT(sd.name FROM specialization_detail
                         WHERE sd.career_id = row.id)
    FROM Career_Master CM
    LEFT JOIN Area_Catalog AC on AC.area_id = CM.area_id
    WHERE school_id = school_id_in
    GROUP BY 1,2;
END //
3
  • It's a many-to-one relationship. A career can have zero or more specializations Jul 21, 2021 at 3:23
  • And in the documentation examples they use the same syntax as I DECLARE cur1 CURSOR FOR SELECT i FROM test.c1; Jul 21, 2021 at 3:36
  • But the FETCH and test for completion are different.
    – Rick James
    Jul 21, 2021 at 3:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.