Query with field as IN value

Question

I’m trying to do a survey using INNER JOIN in table service, the scheduler field.serv_id is a string "vector" and its value in the database is: '1, 2'
I did 3 Indsert’s in the service, the only problem is that the IN only works when using (1.2) but in this case I can not define this way because each schedule has the id of a certain service

Even converting the whole field the result is the same as I did not find no splt function in mysql the case remains unsolved. If it is necessary to do the process in back-end I am using the Visualbasic programming language. Follows 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 

Answer 1

In the database you have something like 1,2,3,4 and you need to search for only one value, right? Really ideal would it not happen, you could have a relationship table to store the service id and agenda id, avoiding this type of field vector. But since I don’t think you intend to alter the structure, I suggest a solution alternative (gambiarra): pass to store the vector starting and ending by comma, ex ,1,2,3,4,; with this, you can search for a number also starting and ending by 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 the CONCAT to change the service id only in the search and the LOCATE in place of the in to validate whether the code (string) exists in the vector of ids (function returns > 0 if there is a substring in string original).

NOTE: do not indicate you directly search the code (id) because it can return wrong data (e.g., searching by 1 would return truth to id 11)