Personal oops. I'm having difficulty setting up a query in MySQL.
It is as follows, I have a table with the following structure
The time_array and time_azul columns store user IDs of the system.
I need to look in the columns time_blue and time_blue a specific ID. For example, my user ID is number 5. I need to check in which lines there are number 5 in the orange_time or the time_blue.
I could not mount anything, sorry: (
Assuming your table structure looks 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 MySQL , you can use the FIND_IN_SET() to verify that a particular value is contained in a comma-separated list of values (% with%).
To retrieve the% with% of the , of identifier Partidas participated in Jogador :
SELECT * FROM tb_partida WHERE FIND_IN_SET( '5', time_laranja );
Output:
| 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 retrieve the% with% of the 5 of identifier Time Laranja participated in Partidas :
SELECT * FROM tb_partida WHERE FIND_IN_SET( '5', time_azul );
Output:
| 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 in SQLFidle.com
I used the FIND_IN_SET function of Mysql Documentation for anyone: link
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 to study how to do a good bank modeling and the rules that define forma normal .