Results 1 to 1 of 1
Jul 16, 2011, 00:09 #1
- Join Date
- Jul 2011
- 0 Post(s)
- 0 Thread(s)
design of a stored procedure with HAVING LIKE
I' m trying to write a stored procedure that will search a fairly simple database with
a USER table (user_id,name,...)
a USER_TYPE table (user_id,type_id) - multi to multi
a TYPE table (type_id,type_name)
a USER_GAME (user_id,game_id) -multi to multi
a GAME table (game_id,game_name)
A same user can have several games. Now, I want to be able to get the user according to a particular type and also according to a/some particular game(s), so that for example I can get all the user with, say type1, and with the games, say game2 and game5. I think I can get round the problem of several game names by passing them as a string parameter and do some kind of HAVING LIKE condition (I call get_user_spec('type1' , 'game3,game5') for example).
So far I get to that point:
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_user_spec`(
IN inTypeName VARCHAR(50),
IN inGameName VARCHAR(150)
PREPARE statement FROM
"SELECT u.user_id,t.type_name,GROUP_CONCAT(g.game_name) AS game
FROM user u
INNER JOIN user_type ut
INNER JOIN type t
LEFT JOIN user_game ug
LEFT JOIN game g
GROUP BY u.user_id
HAVING game LIKE CONCAT('%',?,'%')
ORDER BY u.user_id";
EXECUTE statement USING @p1,@p2;
But my real problem is that if I don't pass any game name, I then want to get all users with type1 (I then call get_user_spec('type1' , NULL). But I am then not getting anything as the procedure sees
HAVING game LIKE CONCAT('%',NULL,'%').
I hope that was clear enough. If anybody has any suggestions to get around that problem, I would be very grateful.
Thank you very much.