query in 2 tables same time

1

Good night, folks, I need help in a query 2 tables if there is an id in the second price appears if price does not appear in the first table ..

example

table1 table2

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

id | description | price id | dtini | dtfim | price

17 | bread with salt | 9.00 20 | 10/26/2017 | 10/30/2017 | 1.99

18 | cake | 10,80 17 | 10/26/2017 | 10/11/2017 | 6.10

20 | Okra | 2.99

23 | Tomato | 3.50

so that when you execute the query this way

table1

id | description | price

17 | bread with salt | 6.10

18 | cake | 10.80

20 | Okra | 1.99

23 | Tomato | 3.50

so I'm going to give a select in table1 and list everything but if the id exists in table2 prepend the price of table2, if there is no price to maintain table1.

Who can help ...

can be in postgres, fb, mysql.

    
asked by anonymous 26.10.2017 / 04:39

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 I'm going to give a select in table1 and list everything but if id   table2 to prevail the price of table 2, if there is no   price of table1

And I did, and 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 honeycomb from the second table :)

    
26.10.2017 / 05:59