Inner Join with like, Mysql and Datatable

Asked

Viewed 364 times

1

Speak people, I have a problem at the time of searching a field in the datatable, i have a table of contributors, this is linked with the contributors_param, contributors_end and contributors_tel. I’m trying to do a search using like and Inner Join, until then the search by name, type_param, zip code and street, is working perfectly... now, the type_tel and phone_tel is repeating!!!!!

SELECT  n_contribuintes.id_cont, n_contribuintes.nome_cont, n_contribuintes_param.tipo_param,
    n_contribuintes_end.cep_end, n_contribuintes_end.rua_end, n_contribuintes_tel.tipo_tel, 
    n_contribuintes_tel.fone_tel FROM n_contribuintes 
    INNER JOIN n_contribuintes_param ON n_contribuintes.id_cont = n_contribuintes_param.id_cont_param  
    INNER JOIN n_contribuintes_end ON n_contribuintes.id_cont = n_contribuintes_end.id_cont_end  
    INNER JOIN n_contribuintes_tel ON n_contribuintes.id_cont = n_contribuintes_tel.id_cont_tel
            AND nome_cont LIKE '%avulso%' 
            OR tipo_param LIKE '%avulso%' 
            OR cep_end LIKE '%avulso%'
            OR rua_end LIKE '%avulso%'
            OR tipo_tel LIKE '%avulso%'
            OR fone_tel LIKE '%avulso%'
            WHERE id_esc_cont = 30
            GROUP BY id_cont

Testing:::

img1

img2

img3

inserir a descrição da imagem aqui

inserir a descrição da imagem aqui

mysql sql database operator-like

  • The INNER JOIN repeats the lines for each occurrence, in your case of phone and address. You want to show only 1?

  • include the images in the question, many hosts are blocked and many people may not see, and it becomes clearer

  • @Sorack yes, only what I research, but he repeats them all...

  • @Ricardopunctual I tried, but it’s not working.

1 answer

1


Separate your responsibilities from your own query. Leave on ON only the link of the tables:

SELECT n_contribuintes.id_cont,
       n_contribuintes.nome_cont,
       n_contribuintes_param.tipo_param,
       n_contribuintes_end.cep_end,
       n_contribuintes_end.rua_end,
       n_contribuintes_tel.tipo_tel,
       n_contribuintes_tel.fone_tel 
  FROM n_contribuintes 
       INNER JOIN n_contribuintes_param ON n_contribuintes.id_cont = n_contribuintes_param.id_cont_param 
       INNER JOIN n_contribuintes_end ON n_contribuintes.id_cont = n_contribuintes_end.id_cont_end 
       INNER JOIN n_contribuintes_tel ON n_contribuintes.id_cont = n_contribuintes_tel.id_cont_tel 
 WHERE id_esc_cont = 30
  AND (nome_cont LIKE '%avulso%'
    OR tipo_param LIKE '%avulso%'
    OR cep_end LIKE '%avulso%'
    OR rua_end LIKE '%avulso%'
    OR tipo_tel LIKE '%avulso%'
    OR fone_tel LIKE '%avulso%')
 GROUP BY id_cont

  • Cool!!! worked, was using this way... AND nome_cont LIKE '%' AND n_contributors.id_esc_cont = 30 OR type_param LIKE '%' AND n_contributors.id_esc_cont = 30 OR cep_end LIKE '%' AND n_contributors.id_esc_cont = 30 OR rua_end LIKE '%' AND n_contributors.id_esc_cont = 30 OR type_tel LIKE '%' AND n_contributors.id_esc_cont = 30 OR fone_tel LIKE '%% ' AND n_contributors.id_esc_cont = 30, and it also worked, but yours is cleaner kkk, thanks!

Browser other questions tagged mysql sql database operator-like

You are not signed in. Login or sign up in order to post.