Query mysql with subquery Join

Asked

Viewed 577 times

2

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

How can I do?

  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')
mysql

1 answer

1

Just a little different from what you’ve 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

  • Thanks for the answer, but doing the test noticed that this way when there is no corresponding data in the table of notes the query does not return any record, IE, if the sale has no note it will not be shown, would have some idea of how to get around this situation?-

  • In place of JOIN, place LEFT JOIN.

  • Although, if the sale doesn’t have notes, it doesn’t make sense to count the notes, it’s not true?

  • I edited the answer.

  • So. the function of this query would be to list sales with notes and without notes, for this I need to make this comparison between the two tables, the difficulty is that some sales may have more than one note, otherwise you could make a trigger and record the note code on the sales chart... I’ll keep trying here, even with the left Join didn’t work, having doesn’t obey the filter.

  • Thank you, now it’s worked!

Show 1 more comment

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.