Result of a SELECT to use in another SELECT?

Question

Result of a SELECT to use in another SELECT?

Navigation

#1 by (2 votes)

0

I know the title is a bit confusing, but I'll explain it here:

I have a select, which displays to me the stores that have a promotion ( CONSULTATION 1 ):

SELECT * FROM empresas INNER JOIN promocoes ON empresas.id = promocoes.id_empresa WHERE promocoes.ativo = 'sim' GROUP BY empresas.nome_fantasia

In my second select, I have the following query ( CONSULT 2 ):

SELECT *, empresas.id AS id_emp, FROM empresas LEFT OUTER JOIN promocoes ON promocoes.id_empresa = empresas.id WHERE promocoes.id_empresa IS NULL OR promocoes.ativo = 'nao' GROUP BY empresas.nome_fantasia

It works perfectly, but if a company has 2 promotions, and you disable a promotion from it in the control panel, the company appears 2 times:

in the first query because you have an active promotion
in the second query because it has an inactive promotion.

So, I wanted to know: how to make a company that has an active promotion (it appears in CONSULT 1), does not appear in CONSULT 2?

php mysql

asked by anonymous 02.07.2017 / 04:35

1 answer

HTTP / 1.1 302 Found Is it normal or does it have a problem? Permissions file AndroidManifest.xml [duplicate]

score 2 · Accepted Answer

I would try to use NOT EXISTS . Without the commands to create and minimally populate the tables, I tried to write down how the query would look like. It's more or less this:

SELECT DISTINCT empresas.nome_fantasia
FROM empresas
WHERE NOT EXISTS
(SELECT * FROM promocoes WHERE empresas.id = promocoes.id_empresa AND promocoes.ativo = 'sim')