SQL Stored Queries - use result of query as boolean based on existence of records
- by Christian Mann
Just getting into SQL stored queries right now... anyway, here's my database schema (simplified for YOUR convenience):
member
------
id INT PK
board
------
id INT PK
officer
------
id INT PK
If you're into OOP, Officer Inherits Board Inherits Member. In other words, if someone is listed on the officer table, s/he is listed on the board table and the member table. I want to find out the highest privilege level someone has. So far my SP looks like this:
DELIMITER //
CREATE PROCEDURE GetAuthLevel(IN targetID MEDIUMINT)
BEGIN
IF
SELECT `id`
FROM `member`
WHERE `id` = targetID;
THEN
IF
SELECT `id`
FROM `board`
WHERE `id` = targetID;
THEN
IF
SELECT `id`
FROM `officer`
WHERE `id` = targetID;
THEN
RETURN 3; /*officer*/
ELSE
RETURN 2; /*board member*/
ELSE
RETURN 1; /*general member*/
ELSE
RETURN 0; /*not a member*/
END //
DELIMITER ;
The exact text of the error is
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near
'SELECT id
FROM member
WHERE id = targetID;
THEN
IF
SEL' at line 4
I suspect the issue is in the arguments for the IF blocks. What I want to do is return true if the result-set is at least one -- i.e. the id was found in the table.
Do any of you guys see anything to do here, or should I reconsider my database design into this:?
person
------
id INT PK
level SMALLINT
