adasd

CREATE VIEW view1 AS
SELECT s.*
FROM taxespayments tp
RIGHT JOIN students s ON s.id=tp.student_id
WHERE tp.id IS NULL;

SELECT * FROM view1;

################################################

DELIMITER //
CREATE PROCEDURE getGroupsByCoach(IN nameParam VARCHAR(255))
BEGIN

DECLARE coachId INT DEFAULT 0;

SET coachId = (SELECT id FROM coaches WHERE name=nameParam);

SELECT *
FROM sports sp
JOIN sportgroups sg ON sg.sport_id=sp.id
WHERE sg.coach_id=coachId;

END
//
DELIMITER ;

CALL getGroupsByCoach('Ivan Todorov Petkov');

################################################

DELIMITER //
CREATE PROCEDURE isStudentInGroup(IN nameParam VARCHAR(255))
BEGIN

DECLARE studentId INT DEFAULT 0;
DECLARE checker INT DEFAULT 0;

SET studentId = (SELECT id FROM students WHERE name=nameParam);

SET checker = (SELECT EXISTS(
SELECT *
FROM sports sp
JOIN sportgroups sg ON sg.sport_id=sp.id
JOIN students st ON st.id IN (SELECT student_id FROM student_sport WHERE sportGroup_id = sg.id)
WHERE st.id = studentId));

IF(checker = 0) THEN SELECT 'No' AS is_student_in_a_group;
ELSE SELECT 'Yes' AS is_student_in_a_group;
END IF;

END
//
DELIMITER ;

#drop procedure isStudentInGroup

CALL isStudentInGroup('Ivan Iliev Georgiev');