Search multiple Mysql tables

Question

I have the following SQL

SELECT * FROM 
cliente as c 
INNER JOIN cliente_contato as ct ON ct.nome LIKE '%{$pesquisa}%' OR ct.telefone LIKE '%{$pesquisa}%'
JOIN cliente_ip as cip ON cip.detalhes LIKE '%{$pesquisa}%'
INNER JOIN cliente_mac as mac ON mac.detalhes LIKE '%{$pesquisa}%'
INNER JOIN cliente_pppoe as pppoe ON pppoe.usuario LIKE '%{$pesquisa}%'
WHERE c.nome LIKE '%{$pesquisa}%'

I would like to search in several tables the same TAG, if you have, returns the Results... But there always returns zero... What will be? Someone help me?

Table structure:

Answer 1

Analyzing your SQL you can see that it wasn’t very clear to you how to make relationships in JOINS, recommend you read here: What is the difference between INNER JOIN and OUTER JOIN?

Leaving for your trouble, I’d do something like this:

SELECT * FROM cliente as c 
INNER JOIN cliente_contato as ct ON ct.id_cliente = c.id
INNER JOIN cliente_ip as cip ON cip.id_cliente = c.id
INNER JOIN cliente_mac as mac ON mac.id_cliente = c.id
INNER JOIN cliente_pppoe as pppoe ON pppoe.id_cliente = c.id

This way is linked all tables by your id_cliente ensuring data integrity, if any of these tables are not required in your result you can exchange INNER for LEFT.

Leaving for your research:

WHERE ct.nome LIKE '%{$pesquisa}%' 
   OR ct.telefone LIKE '%{$pesquisa}%'
   OR cip.detalhes LIKE '%{$pesquisa}%'
   OR mac.detalhes LIKE '%{$pesquisa}%'
   OR pppoe.usuario LIKE '%{$pesquisa}%'
   OR c.nome LIKE '%{$pesquisa}%'

That way it will bring the result if only one of the conditions meet.

Final Code

SELECT * FROM cliente as c 
INNER JOIN cliente_contato as ct ON ct.id_cliente = c.id
INNER JOIN cliente_ip as cip ON cip.id_cliente = c.id
INNER JOIN cliente_mac as mac ON mac.id_cliente = c.id
INNER JOIN cliente_pppoe as pppoe ON pppoe.id_cliente = c.id
WHERE ct.nome LIKE '%{$pesquisa}%' 
   OR ct.telefone LIKE '%{$pesquisa}%'
   OR cip.detalhes LIKE '%{$pesquisa}%'
   OR mac.detalhes LIKE '%{$pesquisa}%'
   OR pppoe.usuario LIKE '%{$pesquisa}%'
   OR c.nome LIKE '%{$pesquisa}%'

Answer 2

With the use of the Inner Join if one of the tables does not have the result it will not return any line. Try with the LEFT JOIN.

In the Inners you have to use the condition on to determine when there will be the junction, in case I put that each table has the id_client there I make the junction with the client using the id and your LIKE I put in the condition WHERE

SELECT * FROM 
cliente as c 
LEFT JOIN cliente_contato as ct ON ct.id_cliente = c.id
LEFT JOIN cliente_ip as cip ON cip.id_cliente = c.id
LEFT JOIN cliente_mac as mac ON mac.id_cliente = c.id
LEFT JOIN cliente_pppoe as pppoe ON pppoe.id_cliente = c.id
WHERE c.nome LIKE '%{$pesquisa}%' OR ct.nome LIKE '%{$pesquisa}%' OR ct.telefone LIKE '%{$pesquisa}%' OR cip.detalhes LIKE '%{$pesquisa}%' OR mac.detalhes LIKE '%{$pesquisa}%' OR pppoe.usuario LIKE '%{$pesquisa}%'

The right thing would be for you to do different queries and do different executions!

Answer 3

I would do it this way:

SELECT c.*, ct.*, cip.*, mac.*, pppoe.* 
FROM cliente c 
LEFT JOIN cliente_contato ct ON (ct.id_cliente=c.id)
LEFT JOIN cliente_ip cip ON (cip.id_cliente=cip.id)
LEFT JOIN cliente_mac mac ON (mac.id_cliente=cip.id)
LEFT JOIN cliente_pppoe pppoe ON (pppoe.id_cliente=cip.id)
WHERE c.nome LIKE '%$pesquisa%'
OR    ct.nome LIKE '%$pesquisa%' 
OR    ct.telefone LIKE '%$pesquisa%'
OR    cip.detalhes LIKE '%$pesquisa%'
OR    mac.detalhes LIKE '%$pesquisa%'
OR    pppoe.usuario LIKE '%$pesquisa%';