List Mysql tables and filter by date

Question

I need only a final value, the commission value for products with extra commission in a date range and a specific seller, the information is in 3 tables in the BD:

Below is the table 'Commission products_extra', the column 'Open Data_shows when the product started to have extra commission, and 'Closing Data_when it no longer has, the column value is how much commission is earned per unit sold (every time you put extra commission on a product it creates a new record instead of changing the old one, so you can have a history)

This is Itens_venda, it shows the products of sales

And that’s the sales one (employee id and sale date are here)

So far the code I wrote with the help was as follows:

SELECT itens_venda.Id_produto, Quantidade, Valor, Id_funcionario, data_venda
FROM genius.Itens_venda
JOIN genius.vendas ON itens_venda.Id_venda = vendas.Id 
JOIN produtos_comissao_extra ON produtos_comissao_extra.Id_produto = itens_venda.Id_produto
WHERE data_venda BETWEEN Data_abertura AND ifnull(Data_fechamento,curdate()) AND Id_funcionario = 5
ORDER BY Data_fechamento DESC; 

The result is this:

It’s almost what I need, just need to filter by a specific date interval, for example, I only want month 7, but have to follow everything that has already been written in the code, I just have to filter the result I already have

Answer 1

You can start by using the INNER JOIN to join their tables, use the clause WHERE to reference a specific field (specific seller) the clause BETWEEN for a range of dates (extra commission on a range of dates) and a ORDER BY to optimize your query if you have more than one result.

Here’s a kick for you to get.

   select * from Vendas V
    join Itens_venda IV
    on IV.Id_venda = V.Id 
    join produtos_comissao_extra PC
    on PC.Id_produto = IV.Id_produto
    where V.Id_funcionario = 1
    order by Data_fechamento desc

If you need anything else, it would be good to edit your question in more detail.