consulta 2 tabelas if existe id na segunda aparece preco se não aparece preco da primeira tabela

[sreletronica 0]

boa noite, pessoal, preciso de ajuda em uma consulta 2 tabelas if existe id na segunda aparece preco se não aparece preco da primeira tabela..

exemplo

 

tabela1                                                                                                                                              tabela2

------------------------------------------------------------------------                     -----------------------------------------------------------------------------------

id                | descricao                                                 | preco                                                           id           |           dtini      |       dtfim                    | preco

17               | pao de sal                                                | 9,00                                                              20         |   26/10/2017 | 30/10/2017              | 1,99

18               | bolo                                                           | 10,80                                                           17          | 26/10/2017  | 10/11/2017               | 6,10  

20               | Quiabo                                                      | 2,99

23               | Tomate                                                     | 3,50

 

 

de modo que qdo execultar a consulta resulte desta forma

 

tabela1                                                                                                                                            

------------------------------------------------------------------------                    

id                | descricao                                                 | preco                                                          

17               | pao de sal                                                | 6,10                                                             

18               | bolo                                                           | 10,80                                                          

20               | Quiabo                                                      | 1,99

23               | Tomate                                                     | 3,50

 

de modo que vou dar um select na tabela1 e listar tudo mas se a id existir na tabela2 pregar o preco da tabela2, se não existir manter o preco da tabela1..

 

Quem puder ajudar...

 

pode ser em postgres, fb,, mysql..

 

 

 

[Motta 645]

 

--Exemplo em Oracle

--LEFT OUTER JOIN operation
--A LEFT OUTER JOIN is one of the JOIN operations that allow you to specify a join clause.
--It preserves the unmatched rows from the first (left) table, joining them with a NULL row
--in the shape of the second (right) table.
--https://docs.oracle.com/javadb/10.6.2.1/ref/rrefsqlj18922.html#rrefsqlj18922

--nvl
--NVL lets you replace null (returned as a blank) with a string in the results of a query.
--If expr1 is null, then NVL returns expr2. If expr1 is not null, then NVL returns expr1.
--https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions105.htm

select tabela1.id,tabela1.descricao,
       nvl(tabela2.preco,tabela1.preco) preco
from tabela1
  LEFT OUTER JOIN tabela2
  on tabela2.id = tabela1.id

 

[sreletronica 0]

vou testar

 

thank you!!!

[sreletronica 0]

7 horas atrás, Motta disse:

 

--Exemplo em Oracle

--LEFT OUTER JOIN operation
--A LEFT OUTER JOIN is one of the JOIN operations that allow you to specify a join clause.
--It preserves the unmatched rows from the first (left) table, joining them with a NULL row
--in the shape of the second (right) table.
--https://docs.oracle.com/javadb/10.6.2.1/ref/rrefsqlj18922.html#rrefsqlj18922

--nvl
--NVL lets you replace null (returned as a blank) with a string in the results of a query.
--If expr1 is null, then NVL returns expr2. If expr1 is not null, then NVL returns expr1.
--https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions105.htm

select tabela1.id,tabela1.descricao,
       nvl(tabela2.preco,tabela1.preco) preco
from tabela1
  LEFT OUTER JOIN tabela2
  on tabela2.id = tabela1.id

 

     valeu mesmo, obrigado

 

fiz uns ajustes no firebird

 

e ficou assim...

 

select a.prd_codigo_barras, a.prd_descricao,
       coalesce( b.preco , a.preco_atual ) preco
from cap_produto a                                                
  LEFT OUTER JOIN promoii b
  on b.barras = a.prd_codigo_barras where a.prd_codigo_barras in('00000000000017', '00000000007306', '00000000000024')
  and b.dtinicio <= current_date and b.dtfim >= current_date

 

era isto que precisava...

 

thanks....