Query mysql with join of subquery

Navigation
2

I need to create a query that I can filter sales that have issued notes, in my structure there are 2 tables: the sales table and the nfe table. The two are listed by the cod_venda field.

How can I do this? 

  SELECT v.cod_venda
  FROM vendas v

  JOIN
     ( SELECT COUNT(num_nfe) AS qnt_notas
            , cod_venda
         FROM nfe
        GROUP 
           BY nfe.cod_venda 
     ) notas
    ON nota.cod_venda = v.cod_venda
 WHERE  notas.qnt_notas > 0 
   AND nfe.status in ('emitida', 'cancelada')
    
asked by anonymous 03.06.2018 / 01:21

1 answer

1

Just a little different from what you've already done, 

SELECT v.cod_venda, COALESCE(notas.qnt_notas, 0) qnt_notas
FROM vendas v
LEFT JOIN (SELECT COUNT(num_nfe) qnt_notas, cod_venda
           FROM nfe
           WHERE status IN ('emitida','cancelada')
           GROUP BY cod_venda) notas ON notas.cod_venda = v.cod_venda
    
03.06.2018 / 01:32
What is the difference between HTMLCollection, NodeList and Object? Modular Maven project with Java10 modular does not find the java module