Search by word taking into account relation between 3 tables

Question

Search by word taking into account relation between 3 tables

Navigation

#1 by (2 votes)

2

I am setting up a search system for a business guide and at the time of searching I am 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 one that does not have "pizza" in the name is part of the "pizzeria" category, for example, and bring it.

This is the framework I'm using to relate a business to the categories:

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

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

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

This is the search I'm doing:

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

mysql

asked by anonymous 10.03.2016 / 23:27

1 answer

Algorithm Shutting down PC screen after execution Including external .js files

score 2 · Accepted Answer

You can put all conditions in 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 WHERE contain all valid conditions. The most appropriate% of% depends only on how you will deal with duplicates.

Returning only one line per company:

If it is to return only one company to several categories, you can do this:

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 JOIN . It is there only to complement the results and facilitate the debug .

And to count the records, for pagination:

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%"