MySQL query to retrieve a list of data using logical operations

Question

The following is a list of the various books that customers read in the library. Values ​​are stored with a capacity of 2 in the bookType column.

I need to get a list of books with combinations of people who read only Novel Or only Fairytale Or only BedTime Or both Novel + Fairytale from a database with a logical operation request.

Selection list for the following combinations:

• person who reads only the novel (stored in DB as 1)
• a person who reads both a novel and a fairy tale (stored in the database as 1 + 2 = 3)
• the person who reads all three versions of {new + fairy tale + bed} (stored in DB as 1 + 2 + 4 = 7)

Their number is stored in the database in the BookType column (marked in red in the figure)

How can I get the above list using MySQL query

In this example, I need to get users, such as new readers (1,3,5,7).

Answer 1

The heart of this question is the conversion of decimal to binary, and mysql has a function to execute only - CONV (num, from_base, to_base); In this case, from_base will be 10, and to_base will be 2. I would wrap this in UDF So

 MariaDB [sandbox]> select id,username -> from users -> where id < 8; +----+----------+ | id | username | +----+----------+ | 1 | John | | 2 | Jane | | 3 | Ali | | 6 | Bruce | | 7 | Martha | +----+----------+ 5 rows in set (0.00 sec) MariaDB [sandbox]> select * from t; +------+------------+ | id | type | +------+------------+ | 1 | novel | | 2 | fairy Tale | | 3 | bedtime | +------+------------+ 3 rows in set (0.00 sec) 

This UDF

 drop function if exists book_type; delimiter // CREATE DEFINER=`root`@`localhost` FUNCTION `book_type`( `indec` int ) RETURNS varchar(255) CHARSET latin1 LANGUAGE SQL NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT '' begin declare tempstring varchar(100); declare outstring varchar(100); declare book_types varchar(100); declare bin_position int; declare str_length int; declare checkit int; set tempstring = reverse(lpad(conv(indec,10,2),4,0)); set str_length = length(tempstring); set checkit = 0; set bin_position = 0; set book_types = ''; looper: while bin_position < str_length do set bin_position = bin_position + 1; set outstring = substr(tempstring,bin_position,1); if outstring = 1 then set book_types = concat(book_types,(select trim(type) from t where id = bin_position),','); end if; end while; set outstring = book_types; return outstring; end // delimiter ; 

Results in

 +----+----------+---------------------------+ | id | username | book_type(id) | +----+----------+---------------------------+ | 1 | John | novel, | | 2 | Jane | fairy Tale, | | 3 | Ali | novel,fairy Tale, | | 6 | Bruce | fairy Tale,bedtime, | | 7 | Martha | novel,fairy Tale,bedtime, | +----+----------+---------------------------+ 5 rows in set (0.00 sec) 

Note the loop in the UDF to go through the binary string and that position 1 refers to the identifiers in the lookup table; I leave it to you to code errors and clean up.