Help with SQL query

Question

I have 3 tables:

enterprise:

id integer
nome string

category:

id integer
idEmpresa integer
nome string

tag:

id integer
idEmpresa integer
nome string

I am consulting as follows:

SELECT
distinct(e.nome),
e.id,
e.logo
FROM empresas e,
tags t
where (e.nome like '%texto%'
or e.subcategoria like '%texto%'
or t.tag like '%texto%')
and t.idEmpresa = e.id
and e.logo is not null
order by rand()

It queries correctly, but only if there is a registered tag linked to the company, if it does not exist, it ignores the registration.

How to search a company by name, category or tag, regardless of having a registration tag?

Answer 1

Use the clause LEFT JOIN, because it will take all independent tag registrations or not. Also, using JOIN it is no longer necessary to make projection between the tables filtering by WHILE, that is to say, ... and t.idEmpresa = e.id ... it is no longer necessary.

SELECT distinct(e.nome), e.id, e.logo
FROM empresas e
LEFT JOIN tags t ON  e.id = t.idEmpresa
where (e.nome like '%texto%'
or e.subcategoria like '%texto%'
or t.tag like '%texto%')
and e.logo is not null
order by rand()

Following are other types of Union, which can help you solve other problems.