Select Lotofácil games that must contain dozens of specific

Question

I have a Mysql Database containing all combinations of Lotofácil, exemplified below:

I am trying to recover, for example, the games that contain the tens 11, 17 and 21, obligatorily in the same game.

I’ve tried using one:

SELECT * FROM lfv2
  WHERE Bol1 IN(11,17,21)
    AND Bol2 IN(11,17,21)
    AND Bol3 IN(11,17,21)
    AND Bol4 IN(11,17,21)
    AND Bol5 IN(11,17,21)
    AND Bol6 IN(11,17,21)
    AND Bol7 IN(11,17,21)
    AND Bol8 IN(11,17,21)
    AND Bol9 IN(11,17,21)
    AND Bol10 IN(11,17,21)
    AND Bol11 IN(11,17,21)
    AND Bol12 IN(11,17,21)
    AND Bol13 IN(11,17,21)
    AND Bol14 IN(11,17,21)
    AND Bol15 IN(11,17,21);

However nothing is returned. But there are games that contain these dozens, as is the case of the game that occupies the position 3.268.760, among others. Is there any way for Mysql to return this query ?

Answer 1

In that case you’re stating that all columns from Bol1 to Bol15 must be either 11 or 17 or 21. You won’t be able to solve this problem this way.

Create 3 validation columns (for each number) with the CASE, example:

SELECT lf.* 
CASE 
   WHEN lf.bol1 = 11 THEN 'X'
   WHEN lf.bol2 = 11 THEN 'X'
   **... Aqui você faz o WHEN de todas colunas**

   ELSE '-' 
END validacao_1,
CASE 
   **...**
END validacao_2,
CASE 
   **...**
END validacao_3   
   
FROM lfv2 lf

After creating the 3 CASES create a SUBSELECT sort of like this:

SELECT res.*
FROM (SELECT *)  res   **... Entre parentes o SELECT acima**
WHERE res.validação_1 = 'X'
AND res.validação_2 = 'X'
AND res.validação_3 = 'X'

This will display the result you want.