Select data even if it is not in all tables

Asked

Viewed 29 times

1

I have the following Query:

SELECT 
    public.suprimento.suprimento, 
    public.estoque.numeroserie, 
    public.fornecedor.nome as fornecedor, 
    public.estoque.data as dataentrada, 
    public.estoque.numeronotafiscal,
    public.empresa.nome as empresa,
    public.entrega.data as datasaida,
    public.entrega.notafiscalhss,
    public.entrega.usuario,
    public.entrega.usuariohss,
    public.lotesretornosuprimento.dataretorno,
    public.lotesretornosuprimento.laudo,
    public.lotesgarantiastatus.numeroserieretornado
FROM
    public.estoque, 
    public.suprimento,
    public.fornecedor,
    public.entregaitem,
    public.entrega,
    public.empresa,
    public.lotesretornosuprimento,
    public.lotesgarantiastatus
where 
    public.suprimento.codigo = public.estoque.codigosuprimento and
    public.estoque.codigofornecedor = public.fornecedor.codigo and
    public.entregaitem.codigoestoque = public.estoque.codigo and 
    public.entregaitem.codigoentrega = public.entrega.codigo and
    public.empresa.codigo = public.entrega.codigoempresa and
    public.lotesretornosuprimento.numeroserie = public.estoque.numeroserie and
    public.lotesgarantiastatus.numeroserieenviado = public.estoque.numeroserie

Which returns me the entire route of the supply from the moment it is purchased to the return to our supplier for correct disposal or for warranty, my need however is also to display the supplies that way purchased but that have not yet been sent to some customer or returned to the supplier, in the way that I have assembled the Query I select only the supplies that had the complete trail:

Buy -> Sell -> Return Supplier

It is possible to adapt this Query to return to me also the following situations:

Buy -> Sell

And also when the supply is only purchased:

Buying

postgresql

1 answer

0

What you should do is put LEFT JOIN in the tables that there is no data, example:

SELECT 
    CAMPO1, CAMPO2, CAMPO3, CAMPO4, CAMPO5, CAMPO6
FROM
    TABELA T1
INNER JOIN 
    TABELA T2 ON T2.CAMPO = T1.CAMPO
LEFT JOIN 
    TABELA T3 ON T3.CAMPO = T2.CAMPO

  • Would you be able to adapt this to my query? I didn’t understand very well how INNER JOIN and LEFT JOIN works

  • I do not know well within these tables what defines Buy, Sell and Return Supplier, but the general idea is that all that involves Buying should be INNER JOIN (which brings all the data that exist in the database) and to Sale and Return Supplier LEFT JOIN (which returns the data when they exist in the database and NULL when it does not give corresponding data in the tables)

Browser other questions tagged postgresql

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