So I have two tables in my database that contain a member id. I'm trying to create a stored function that returns 0 when a member is NOT in table A but IS in table B.
Here's my code:
Delimiter //
CREATE FUNCTION DoesMemberExist ( mid char(10))
RETURNS INTEGER
READS SQL DATA
BEGIN
IF(((SELECT COUNT(*)
FROM members
WHERE member_id = mid) = 0) AND ((SELECT COUNT(*)
FROM rent_equipment_log
WHERE member_id = mid) > 0)) THEN
RETURN 0;
ELSE RETURN 1;
END IF;
END;
The error I'm getting is 1064 and it's saying there's a problem with my syntax but I can't figure out whats wrong? Thanks in advance!
You can utilize EXISTS() instead. Also, END IF was missing to end the IF clause.
DELIMITER //
CREATE FUNCTION DoesMemberExist ( mid char(10))
RETURNS INTEGER
READS SQL DATA
BEGIN
IF( NOT EXISTS(SELECT 1 FROM members
WHERE member_id = mid)
AND
EXISTS(SELECT 1 FROM rent_equipment_log
WHERE member_id = mid) ) THEN
RETURN 0;
ELSE RETURN 1;
END IF; -- We need to END the IF clause
END //
DELIMITER ; -- Redefine the delimiter back to ;
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments