Search by word taking into account the relationship between 3 tables

Question

I’m setting up a search system for a corporate guide and at the time of searching I’m only able to search for a specific word in a table, for example: pizza. But the ideal would be to see if there is a company called "pizza" or if any that does not have "pizza" in the name is part of the category "pizzeria", for example, and bring it.

This is the structure I’m using to relate a company to the categories:

| empresa    |
|------------|
| id_empresa |
| nome       |

---

| categoria    |
|--------------|
| id_categoria |
| nome         |

---

| categoria_empresa    |
|----------------------|
| id_categoria_empresa |
| id_empresa           |
| id_categoria         |

That’s the search I’m doing:

SELECT * FROM empresa WHERE nome LIKE %"pizza"%

Answer 1

You can put all the conditions on WHERE:

SELECT
   empresa.nome, categoria.nome
FROM
   categoria_empresa
   INNER JOIN categoria ON categoria.id_categoria = categoria_empresa.id_categoria
   INNER JOIN empresa   ON empresa.id_empresa     = categoria_empresa.id_empresa
WHERE
   categoria.nome LIKE "%pizza%" OR empresa.nome LIKE "%pizza%" 

Ps: the important thing here is the WHERE contain all valid conditions. The JOIN most suitable only depends on how will handle the duplicities.

Returning only one line per company:

If to return only one company to several categories, you can do so:

SELECT
   empresa.nome, GROUP_CONCAT( categoria.nome ) AS categorias
FROM
   empresa
   LEFT JOIN categoria_empresa ON empresa.id_empresa     = categoria_empresa.id_empresa
   LEFT JOIN categoria         ON categoria.id_categoria = categoria_empresa.id_categoria
WHERE
   categoria.nome LIKE "%pizza%" OR empresa.nome LIKE "%pizza%"
GROUP BY
   empresa.id_empresa

In this case, you can remove the GROUP_CONCAT( categoria.nome ) AS categorias. It is there only to complement the results and facilitate the debug.

And to count the records, for paging:

SELECT
   COUNT( DISTINCT empresa.id_empresa )
FROM
   empresa
   LEFT JOIN categoria_empresa ON empresa.id_empresa     = categoria_empresa.id_empresa
   LEFT JOIN categoria         ON categoria.id_categoria = categoria_empresa.id_categoria
WHERE
   categoria.nome LIKE "%pizza%" OR empresa.nome LIKE "%pizza%"