SQL query help

Navigation
0

I have 3 tables:

Company: 

id integer
nome string

category: 

id integer
idEmpresa integer
nome string

tag: 

id integer
idEmpresa integer
nome string

I'm querying 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 any tag linked to the company, if it does not exist, it ignores the registration.

How can I search for a company by name, category or tag, regardless of whether a tag is registered?

    
asked by anonymous 01.04.2018 / 18:00

1 answer

1

Use the LEFT JOIN clause, as it will pick up all tag-independent registrations or not. Also, using JOIN is no longer required to project between tables by filtering through WHILE , that is, ... and t.idEmpresa = e.id ... is no longer required. 

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 other types of Union, which can help you solve other problems.

    
01.04.2018 / 18:05
Insert record automatically when doing Insert PL / SQL Doubt with scanner