I created a procedure where in I had the value of column with enum datatype asCompleteness_Type` ENUM('B', 'I', 'C', 'BC', 'BI', 'IC', 'BIC')
I wrote a procedure where I would like to display all the video game records which does not have 'I' in completeness_type column. Below is the proceudre I have written
DROP procedure IF EXISTS `Get_Info_By_Games_missing_Something`;
create PROCEDURE `Get_Info_By_Games_missing_Something`(IN Completenessip VARCHAR(15))
video_game_platform_mycollection ON video_game.Game_Id = video_game_platform_mycollection.Game_Id
platform ON video_game_platform_mycollection.Platform_Id = platform.Platform_Id
mycollection ON video_game_platform_mycollection.MyCollection_Id = mycollect `enter code here`ion.MyCollection_Id
where mycollection.Completeness_Type not like concat('%',Completenessip,'%');
I am calling procedure with
call Get_Info_By_Games_missing_Something('I'); --- **It is working**
call Get_Info_By_Games_missing_Something('BI'); -- **Not Working**
call Get_Info_By_Games_missing_Something('BIC'); --**not working**
For the first Example: call Get_Info_By_Games_missing_Something('I');
I would like to display completeness_type column which does not contain 'I' So, the records in completeenss_type column has to be 'B', 'C', 'BC'
For the second call:
Expected output: call Get_Info_By_Games_missing_Something('BI');
I would like to display completeness_type column which does not contain 'B','I' ,'BI','IC', 'BC', 'BIC'So, the records in completenenss_type column has to be 'c'
For the Third call:call Get_Info_By_Games_missing_Something('BIC');
I would like to display completeness_type column which does not contain 'B','I', 'C', 'BI', 'IC','BC', 'BIC' So, the records in completenenss_type column has to be no records
So, could you please tell what is the mistake I am making. I got this query working for rows which does not contain only 'B', 'I' or 'C'. I just want result for rows which does not contain 'BI' or 'B' or 'I' individually
Thanks in advance
I don't have an answer to your exact question, but it seems to be there may be a better way. It's a little unclear what your values correspond to, but one easy way is to have three columns,
C which could be
BIT type. You might have success storing it similar to how Unix permissions are stored; such that a file is stored as BIC so a row of type B would be encoded as 100, and IC would read 011. Depending on a couple of factors this may be better than the individual columns, but as an off-the-cuff answer based on what your queries look like, I suspect the discrete columns are going to be easier to code.
Then, of course, to search for a row with BI you'd search for columns
WHERE `B`=1 AND `I`=1 AND `C`=0 and so on with the other searches. This is will scale better and is a better database design than using ENUM fields.