join two tables with date condition

Question

I need to join two tables, being the first presenting all the records contained in it, with the condition that what is in the second table has a lower price with data_ini and dt_fim using the current_date to define whether this is within the condition or within the current date.

Example:

Table 1:

---------------+------------+------
cod_barras     | descrição  | preco
---------------+------------+------
00000000000017 | PAO DE SAL |  0,50
00000000000122 | PEPSI 1 5L |  5,00
00000000000987 | TAIOBA     |  2,00
00000000001526 | ALHO KG    | 20,00

Table 2:

---------------+-------+------------+-----------
barras         | preco | DT_ini     | DT_FIM
---------------+-------+------------+-----------
00000000000017 |  0,30 | 03/11/2017 | 10/11/2017
00000000001526 | 15,00 | 05/11/2017 | 08/11/2017

whereas the current_date = 04/11/2017, should look like this:

---------------+------------+------
cod_barras     | descrição  | preco
---------------+------------+------
00000000000017 | PAO DE SAL |  0,30
00000000000122 | PEPSI 1 5L |  5,00
00000000000987 | TAIOBA     |  2,00
00000000001526 | ALHO KG    | 20,00

whereas the current_date = 07/11/2017, should look like this:

---------------+------------+------
cod_barras     | descrição  | preco
---------------+------------+------
00000000000017 | PAO DE SAL |  0,30
00000000000122 | PEPSI 1 5L |  5,00
00000000000987 | TAIOBA     |  2,00
00000000001526 | ALHO KG    | 15,00

whereas the current_date = 11/11/2017, should look like this:

---------------+------------+------
cod_barras     | descrição  | preco
---------------+------------+------
00000000000017 | PAO DE SAL |  0,50
00000000000122 | PEPSI 1 5L |  5,00
00000000000987 | TAIOBA     |  2,00
00000000001526 | ALHO KG    | 20,00

That is, I need an item that exists in Table 2 within a specific date to pick the price from Table 2 and not from Table 1.

You will get the value of table 1, if it is not contained in table 2 and within the current date.

Who can help, it’s a little difficult.

Answer 1

Make a left Outer Join by setting the conditions in the Join, and use the coalition for products that have no corresponding records in the table2:

Select
p.cod_barras,
p.descricao,
coalesce(t.preco,p.preco) as preco
from tabela1 p 
left outer join tabela2 t
                on p.cod_barras = t.barras 
                and t.dt_ini <= current_date 
                and t.dt_fim >= current_date
                and t.preco < p.preco --(Só vai buscar na segunda tabela se o preço for menor certo ? caso contrário, só tirar essa linha)

I put in Sqlfiddle: http://sqlfiddle.com/#! 9/410ec0/2

Answer 2

when you do Join, do not direct on the table filter before, then it is easier;

for example

 select*from produto a
 join (select cod_barras, min(valor) valor
           from produtos_preco  
              where data_final>data_atual 
                and data_inicia<data_atual
                group by cod_barras) b on a.cod_barras =b.cod_barras

something like that, as far as I’m concerned; but I found it kind of hard to understand the question :)