SELECT that brings active products in one company and that are not active in all other

You can try something like this:

SELECT * FROM Tabela_Produtos p INNER JOIN Tabela_Filiais f ON f.id = p.filial_id
WHERE p.filial_id = 16 AND p.status LIKE 'Ativo' AND p.id NOT IN (
      SELECT p2.id FROM Tabela_Produtos p2 
      INNER JOIN Tabela_Filiais f2 ON f2.id = p2.filial_id
      WHERE p2.filial_id != 16 AND p2.status LIKE 'Inativo')

Considering that the data is all in a table MRL_PRODUTOEMPRESA your query will be:

SELECT * FROM MRL_PRODUTOEMPRESA 
         WHERE (EMPRESA = 16 AND STATUS LIKE 'A')
         OR (EMPRESA != 16 AND STATUS LIKE 'I');

The query is simpler than the first, but be careful with the modeling of your bank, because putting everything in a same table is not considered a good practice for relational databases and do not advise, especially if it is used for projects professionals.

I had not attempted to inactive detail on ALL other companies. See if you can:

SELECT * FROM MRL_PRODUTOEMPRESA WHERE EMPRESA = 16 AND STATUS LIKE 'A' AND PRODUTO IN
     (SELECT PRODUTO FROM MRL_PRODUTOEMPRESA WHERE EMPRESA != 16 
        AND STATUS LIKE 'I' HAVING COUNT(PRODUTO) >= 
             (SELECT DISTINCT COUNT(PRODUTO)-1 FROM MRL_PRODUTOEMPRESA GROUP BY PRODUTO) 
     GROUP BY PRODUTO) 

• The above query searches all COMPANY PRODUCTS 16 with STATUS Active
• Not in COMPANY list other than 16 with STATUS Inactive
• That contains the total of PRODUCTS registered less 1 (referring to the only Active PRODUCT)

Good evening

Try something like this:

IB OBJECT_ID('TEMPDB..#TEMP_01') IS NOT NULL
   DROP TABLE #TEMP_01

SELECT A.PRODUTO,
       SUM(CASE WHEN A.SITUACAO = 'A' 
                THEN 1 
                ELSE 0 
           END
       ) AS OCORRENCIA_ATIVA
  INTO #TEMP_01
  FROM TB_PRODUTOS A
 WHERE A.LOJA <> 16


SELECT A.*
  FROM TB_PRODUTOS A
  JOIN #TEMP_01    B ON B.PRODUTO = A.PRODUTO
 WHERE A.LOJA = 16
   AND A.SITUACAO         = 'A'
   AND B.OCORRENCIA_ATIVA = 0