Difficulty when mounting SQL query

Question

Whoa, guys. I’m having difficulty to mount a query in Mysql.

The thing is, I have a table with the following structure

The time_orange and time_blue columns store system user ID’s.

I need to search the time_orange and time_blue columns for a specific ID. For example, my user has ID number 5. I need to check which lines have the number 5 in the orange time_or blue time_blue.

I couldn’t put anything together, sorry :(

Answer 1

Assuming your table structure is something like:

CREATE TABLE tb_partida
(
  id INTEGER PRIMARY KEY,
  time_laranja TEXT,
  time_azul TEXT,
  capitao_azul INTEGER,
  capitao_laranja INTEGER,
  placar TEXT,
  finalizado INTEGER
);

Containing the following data:

INSERT INTO
  tb_partida ( id, time_laranja, time_azul, capitao_azul, capitao_laranja, placar, finalizado )
VALUES
  ( 15, '7,5,11,8,9',  '1,4,6,2,3',  6, 11, '2x4', 1 ),
  ( 16, '3,7,11,6,8',  '5,2,4,9,1',  1, 11, '4x5', 1 ),
  ( 17, '6,8,11,2,9',  '1,4,5,3,7',  1, 11, '1x3', 1 ),
  ( 18, '3,9,8,6,11',  '7,2,4,1,5',  1, 11, '4x8', 1 ),
  ( 19,  '2,7,9,1,6', '11,3,4,5,8', 11,  1, '4x8', 1 ),
  ( 20,  '7,5,2,1,6', '4,8,3,11,9', 11,  1, '4x3', 1 ),
  ( 21,  '4,1,3,5,8', '7,2,11,6,9', 11,  1, '9x1', 1 ),
  ( 22,  '4,1,8,7,5', '2,11,3,9,6', 11,  1,   '0', 0 ),
  ( 23, '11,5,8,6,3',  '2,1,9,7,4',  1, 11, '1x6', 1 );

In the MySQL, you can use the function FIND_IN_SET() to check whether a given value is contained in a comma-separated list of values (,).

To recover the Partidas that the Jogador identifier 5 participated in the Time Laranja:

SELECT * FROM tb_partida WHERE FIND_IN_SET( '5', time_laranja );

Exit:

| id | time_laranja |  time_azul | capitao_azul | capitao_laranja | placar | finalizado |
|----|--------------|------------|--------------|-----------------|--------|------------|
| 15 |   7,5,11,8,9 |  1,4,6,2,3 |            6 |              11 |    2x4 |          1 |
| 20 |    7,5,2,1,6 | 4,8,3,11,9 |           11 |               1 |    4x3 |          1 |
| 21 |    4,1,3,5,8 | 7,2,11,6,9 |           11 |               1 |    9x1 |          1 |
| 22 |    4,1,8,7,5 | 2,11,3,9,6 |           11 |               1 |      0 |          0 |
| 23 |   11,5,8,6,3 |  2,1,9,7,4 |            1 |              11 |    1x6 |          1 |

To recover the Partidas that the Jogador identifier 5 participated in the Time Azul:

SELECT * FROM tb_partida WHERE FIND_IN_SET( '5', time_azul );

Exit:

| id | time_laranja |  time_azul | capitao_azul | capitao_laranja | placar | finalizado |
|----|--------------|------------|--------------|-----------------|--------|------------|
| 16 |   3,7,11,6,8 |  5,2,4,9,1 |            1 |              11 |    4x5 |          1 |
| 17 |   6,8,11,2,9 |  1,4,5,3,7 |            1 |              11 |    1x3 |          1 |
| 18 |   3,9,8,6,11 |  7,2,4,1,5 |            1 |              11 |    4x8 |          1 |
| 19 |    2,7,9,1,6 | 11,3,4,5,8 |           11 |               1 |    4x8 |          1 |

See working on Sqlfidle.com

Answer 2

I used the Mysql FIND_IN_SET function Documentation for anyone who wants: https://www.w3schools.com/sql/func_mysql_find_in_set.asp

Answer 3

Select * from tabela where time_azul rlike '([^0-9]|^)5([^0-9]|$)' and time_laranja rlike '([^0-9]|^)5([^0-9]|$)'

This meets your need, however your table is poorly modeled. It is interesting that you study how to make a good bank modeling and the rules that define the forma normal.