How do I link three tables?

Question

I have three tables.

Note that if I perform an INNER JOIN in the three tables, I would return only product 1 (Router), which is present in the three tables. However, I would need him to return all results to me, but without repeating information. Ex.:

If I have 7 Routers in the table estoque1 and 9 routers in the table estoque2, I would need the final result to be 16 routers.

And would also need you to return all results that were related to the table cadastro_produtos.

The expected result would be:

How can I proceed in this case?

Answer 1

you can do it this way:

Select
p.id,
p.nome,
sum(coalesce(e1.quantidade,0)+coalesce(e2.quantidade,0)) as Quantidade
from cadastro_produtos p
left outer join estoque1 e1 on e1.c_prod = p.id
left outer join estoque2 e2 on e2.c_prod = p.id
GROUP BY p.id, p.nome

But I advise to remove one of the stock tables, having only one: estoques where there would be one more column to identify which stock the item belongs to. Thus:

Product

id* | nome | 
1   | roteador |
2   | mouse |

Stockpile

estoque_id*|produto_id*|quantidade|responsavel
1          |    1      |     7    | fulano
1          |    2      |     1    | ciclano
1          |    6      |     3    | xxxxx
2          |    1      |     9    | xxxxx

Answer 2

try so, see if it meets what you want:

Select
p.id,
p.nome,
sum(coalesce(e1.quantidade,0)+coalesce(e2.quantidade,0)) as Quantidade
from cadastro_produtos p
left outer join estoque1 e1 on e1.c_prod = p.id
left outer join estoque2 e2 on e2.c_prod = p.id
GROUP BY p.id HAVING quantidade > 0

Answer 3

Substitur the INNER JOIN for FULL OUTER JOIN will solve your problem.