query in 2 tables at the same time

Asked

Viewed 45 times

1

good evening, guys, need help in a query 2 tables if there is id in the second appears price if there is no price from the first table..

example

Tabela1 table 2

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

id | Description | price id | dtini | dtfim | price

17 | salt bread | 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 | Okra | 2,99

23 | Tomatoes | 3,50

so that when the consultation is carried out it will result in this

Tabela1

id | Description | price

17 | salt bread | 6,10

18 | cake | 10,80

20 | Okra | 1,99

23 | Tomatoes | 3,50

so that I will give a select in Table 1 and list everything but if the id exists in the table2 nail the price of table2, if there is no maintain the price of Table 1..

Anyone who can help...

can be in postgres, fb,, mysql.

query

1 answer

3

So we have two tables:

Table1:

|--------------------------------|
|id      | descricao     | preço |
|--------------------------------|
|1       |Pão de Mel     | 15    |
|2       |Pão Frances    | 7     |
|--------------------------------|

And I have table 2:

Table2:

|--------------------------------|
|id      | descricao     | preço |
|--------------------------------|
|1       |Pão de Mel     | 50    |
|--------------------------------|

Your business rule is:

so that I will select in Tabela1 and list everything but if id Table 2 Preach the price of table 2, if there is no maintain Tabela1

And I did, and I tested the solution:

SELECT t1.descricao, if(t2.preco is null, t1.preco, t2.preco) 
FROM table1 t1 
LEFT JOIN table2 t2 on t1.id = t2.id;

The result was:

|--------------------------------|
|id      | descricao     | preço |
|--------------------------------|
|1       |Pão de Mel     | 50    |
|2       |Pão Frances    | 7     |
|--------------------------------|

He took the price of the honey bread from the second table.:)

  • need more help... in table 2 the items have a promotion term, dtinicio and dtfim ... qdo coloco "Where b.dtinicio <= current_date and b.dtfim >= current_date " only appears the products that are contained in table 2. my code ta so --- select a.prd_codigo_bars Codigo, a.prd_descricao, iif(b.preco is null, a.preco_actual, b.preco) from cap_product a left Join promoii b on b.barra = a.prd_codigo_barra Where b.dtinicio <= current_date and b.dtfim >= current_date

Browser other questions tagged query

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