Dear all,
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)
)
BEGIN
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
ON u.user_id=ut.user_id
INNER JOIN type t
ON ut.type_id=t.type_id
LEFT JOIN user_game ug
ON u.user_id=ug.user_id
LEFT JOIN game g
ON ug.game_id=g.game_id
WHERE t.type_name=?
GROUP BY u.user_id
HAVING game LIKE CONCAT(‘%’,?,‘%’)
ORDER BY u.user_id”;
SET @p1=inTypeName;
SET @p2=inGameName;
EXECUTE statement USING @p1,@p2;
END
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.