Query with field as value of IN

1

I'm trying to do a search using INNER JOIN in the service table , the agenda.serv_id field is a string "vector" and its value in the database is: 1, 2 '
I did 3 insert's in the service, the only problem is that IN only works when I use (1,2) but in that case I can not set it that way because every schedule has the id of some service

Even though converting the field to integer, the result is the same, as I did not find any function of splt in mysql, the case remains unsolvable. If it is necessary to do the process in the back end I am using the VisualBasic programming language. Here is the code:

SELECT
    agenda.agen_id, 
    agenda.agen_data, 
    agenda.agen_turno, 
    group_concat(servico.serv_nome), 
    agenda.agen_total, 
    profissional.prof_comissao, 
    agenda.agen_obs, 
    profissional.prof_nome, 
    cliente.cli_nome 
FROM 
    agenda 
INNER JOIN servico ON servico.serv_id *in (agenda.serv_id)* 
INNER JOIN profissional ON agenda.prof_id = profissional.prof_id 
INNER JOIN cliente ON agenda.cli_id = cliente.cli_id 
    
asked by anonymous 01.10.2018 / 03:50

1 answer

0

In the database you have something like 1,2,3,4 and you need to search for just one value, correct? Really the ideal would be if this does not happen, you could have a relationship table to store the service id and the calendar id, avoiding this kind of vector field. But since I do not think you want to change the structure, I suggest an alternative solution (gambiarra): start storing the vector starting and ending with a comma, ex ,1,2,3,4, ; so you can search for a number also by starting and ending with a comma:

SELECT
    agenda.agen_id, 
    agenda.agen_data, 
    agenda.agen_turno, 
    group_concat(servico.serv_nome), 
    agenda.agen_total, 
    profissional.prof_comissao, 
    agenda.agen_obs, 
    profissional.prof_nome, 
    cliente.cli_nome 
FROM 
    agenda 
INNER JOIN servico ON 1 = 1 --a condição daqui será validada no where
INNER JOIN profissional ON agenda.prof_id = profissional.prof_id 
INNER JOIN cliente ON agenda.cli_id = cliente.cli_id 
WHERE LOCATE(CONCAT(',',servico.serv_id,','), agenda.serv_id) > 0

I used CONCAT to change the service id only in search and LOCATE instead of in to validate if the string tag exists in the ids vector (the function returns

01.10.2018 / 13:17