sub-query with main query parameter

Question

I have this query:

SELECT 
id_arquivo, nome_arquivo, etapa, em_uso, em_uso_por, processo_concluido, obs_temp_etapa_atual, cod_funcionario_dev,  status_devolucao, devolvido_por, SUBSTRING_INDEX(nome_arquivo, " ", 1) as referencia, COUNT(SUBSTRING_INDEX(nome_arquivo, " ", 1)) as QTD_DISP
FROM producao  

WHERE
cod_trabalho = '458' and 
etapa = '33' and 
tipo_arquivo = 'Still' and 
processo_concluido < 1 and 
em_uso < 1 and 
(cod_funcionario_dev IS NULL or cod_funcionario_dev = '1') 

GROUP BY referencia

I already have a field that "counts" the quantity available in the current STEP. I now need to count HOW MANY ARE IN THE PREVIOUS STEPS, and only display the records if there are no records in the previous steps.

It would be something like adding to SQL above:

HAVING

COUNT(SUBSTRING_INDEX(nome_arquivo, " ", 1)) = (
    SELECT   
COUNT(cod_cliente)
FROM producao  

WHERE  cod_trabalho = '458' and 
etapa <= '33' and 
tipo_arquivo = 'Still' and 
processo_concluido < 1 and 
em_uso < 1 and 
(cod_funcionario_dev IS NULL or cod_funcionario_dev = '1') AND
SUBSTRING_INDEX(nome_arquivo, " ", 1) = SUBSTRING_INDEX(nome_arquivo, " ", 1)

GROUP BY SUBSTRING_INDEX(nome_arquivo, " ", 1))

It seems to me that the problem is in passing the aggregate function related to "filename". If I referee the name there, it works (because it returns only 1 record). But I need her to do the query for each record, considering the value returned from the main query.

I believe that if I can get a way to pass an aggregate function, coming from the main query, to the sub-query, I solve my problem.

I have records as for example (simplifying):

NOME_ARQUIVO    ETAPA
REF1 V1          3
REF1 V2          3
REF1 V2          4
REF2 V1          4
REF2 V2          4

We assume the current step is "4", with the above data I want to display only REF2.

The REF1, as it has 1 record in step 4, but 2 in step 3, should not be returned.

Answer 1

Hello,

I think this is what you need, you have to do a subquery to merge the current with the previous one, like this:

        select *
from (SELECT 
SUBSTRING_INDEX(NomeArquivo, ' ', 1) as NomeArquivo, max(etapa) as etapaAtual
FROM producao  
group by SUBSTRING_INDEX(NomeArquivo, ' ', 1) 
) as ProdAtual

 left outer join (
select SUBSTRING_INDEX(NomeArquivo, ' ', 1) as NomeArqAnterior, min(etapa) as EtapaAnterior
from producao
group by SUBSTRING_INDEX(NomeArquivo, ' ', 1)) as ProdAnterior on ProdAtual.NomeArquivo=ProdAnterior.NomeArqAnterior and ProdAtual.etapaAtual>ProdAnterior.EtapaAnterior
where ProdAnterior.EtapaAnterior is null

Only get registration with REF2.