Link to home
Start Free TrialLog in
Avatar of YUSUF ATES
YUSUF ATES

asked on

SQL Procedure

I want to get the category of a student by creating a procedure which has two parameters p_student_id and p_event_id (they are also columns so I put p_).  One way is that I can join all the three tables (student, score, grade_event) and apply the filter for the input parameters to get the category.
STUDENT table has NAME, GENDER, and STUDENT_ID
GRADE_EVENT table has DATE, CATEGORY, and EVENT_ID
SCORE table has STUDENT_ID, EVENT_ID, and SCORE

Do you have any solution?

I tried this one but did not get the category.

DELIMITER $$
CREATE PROCEDURE CATEGORY(IN p_student_id INT, p_event_id  INT)
BEGIN
      BEGIN
      SELECT NAME, GENDER, SCORE, DATE, CATEGORY
      FROM STUDENT st
      JOIN SCORE S
      ON st.student_id = s.student_id
      JOIN grade_event g
      ON s.event_id = g.event_id;
      END;
END
$$
SOLUTION
Avatar of HainKurt
HainKurt
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of YUSUF ATES
YUSUF ATES

ASKER

sample data

STUDENT table
name       VARCHAR(20) NOT NULL,
GENDER        ENUM('F','M') NOT NULL,
student_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (student_id)

GRADE_EVENT table
date     DATE NOT NULL,
category ENUM('T','Q') NOT NULL,
event_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (event_id)

SCORE table
student_id INT UNSIGNED NOT NULL,
event_id   INT UNSIGNED NOT NULL,
score      INT NOT NULL,
PRIMARY KEY (event_id, student_id),
INDEX (student_id),
FOREIGN KEY (event_id) REFERENCES grade_event (event_id),
FOREIGN KEY (student_id) REFERENCES student (student_id)
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
This one works great. Now I get what I want.
Thanks a lot.
I appreciate.