query in 2 tables same time

Question

query in 2 tables same time

Navigation

#1 by (3 votes)

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

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

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.

query

asked by anonymous 26.10.2017 / 04:39

1 answer

How to check if a button is visible? Limit result to a random

score 3 · Answer 1

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 :)