Result of a SELECT to use in another SELECT?

Question

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

I have a select, which displays for 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 (CONSULTATION 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

Works perfectly, but if a company has 2 promotions, and disable a promotion of it in the dashboard, the company appears 2 times:

• at the first consultation, as it has an active promotion
• in the second consultation, as it has an inactive promotion.

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

Answer 1

I would try to use NOT EXISTS. Without the commands to create and popular the tables minimally, I tried to write from the top of my head as such a query would look. That’s about it:

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