SELECT in 3 database tables with specifications

Question

I have three tables in the bank, one for companies, another for employees and another for guides. I am trying to make a SELECT that brings all companies that have any employee linked to it (there is the foreign key in the table), and that DO NOT HAVE the HOLERITE tab in the table "tabs" related to that company.

 Tabela empresas
+--------+------+
| id     | nome |
+----------+----+
| 1      | abc  |
| 2      | hjk  |
| 3      | qwer |
| 4      | tyui |
| 5      | dfg  |
+--------+------+

 Tabela funcionários
+--------+------+-------------+
| id     | nome | empresas_id |
+----------+----+-------------+
| 1      | abc  |5            |
| 2      | hjk  |4            |
| 3      | qwer |3            |
| 4      | tyui |2            |
| 5      | dfg  |1            |
+--------+------+-------------+


     Tabela guias
+--------+----------+-------------+
| id     | tipo     | empresas_id |
+----------+--------+-------------+
| 1      | FGTS     |5            |
| 2      | INSS     |4            |
| 3      | HOLERITE |3            |
| 4      | INSS     |2            |
| 5      | FGTS     |1            |
+--------+----------+-------------+

The SELECT I’m doing is the following:

SELECT
   emp.id, emp.nome_empresa
FROM
   empresas as emp
LEFT JOIN
   funcionarios as f
ON 
   f.empresas_id = emp.id
LEFT JOIN
   guias as g
ON 
   g.empresas_id = emp.id
WHERE
   f.empresas_id = emp.id
AND 
   g.tipo <> 'HOLERITE';

However it is bringing even the companies that have the guide with the type HOLERITE.

Note: The same company may have several types registered in the table, but I need those that do not have the HOLERITE tab registered.

Answer 1

Try:

SELECT emp.id, emp.nome_empresa
FROM empresas as emp JOIN funcionarios as f
ON f.empresas_id = emp.id
WHERE NOT EXISTS (SELETC * FROM guias as g WHERE g.empresas_id = emp.id 
    AND g.tipo = 'HOLERITE');

Take all companies that have an employee and for which there is no HOLERITE guide.