Home / Questions / 286654
Accepted
amblador
Asked: 2020-08-19 20:40:10 +0800 CST 2020-08-19 20:40:10 +0800 CST

SELECT in PostgreSQL for 4 tables

  • 772

I have 4 tables as follows:

-----------   |    -----------   |  -------------  |   ------------   
   caja       |      facturas    |   proveedores   |    productos
-----------   |    -----------   |  -------------  |   ------------
id            |    factura_id    |  proveedor_id   |   producto_id
fecha         |    proveedor_id  |  nombre         |   descripcion
monto         |    producto_id   |  direccion      |   precio
factura_id    |    monto         |                 |   unidad
descripcion   |    impuestos     |                 |

What I want to get is: all cash movements; and those where invoice_id is not null, then it also shows me the name of the supplier and the description of the product.

What I try is the following:

SELECT caja.id, caja.fecha, caja.monto, caja.descripcion, 
       proveedores.nombre, productos.descripcion
FROM caja, facturas, proveedores, productos
WHERE caja.factura_id = facturas.factura_id AND facturas.proveedor_id = 
      proveedores.proveedor_id AND factura.producto_id = 
      productos.producto_id

The query works except for the detail that it only returns the cash movements where there is an invoice_id, and I have movements where there are no invoices, and I would like them to appear in the query as well.

sql

2 Answers

  1. Try this way, to leave it as a JOIN EXPLÍCITO:

    SELECT  caja.id, caja.fecha, caja.monto, caja.descripcion, 
        proveedores.nombre, productos.descripcion
FROM facturas
JOIN caja ON facturas.factura_id = caja.factura_id
JOIN proveedores ON proveedores.proveedor_id = facturas.proveedor_id
JOIN productos ON productos.producto_id = facturas.producto_id
WHERE facturas.factura_id IS NOT NULL;
    • First we choose the desired columns indicating their source table
    • we do 3 JOINSone for each related table
    • We make use of IS NOT NULLto filter the results of those whose invoice id is different from null

    In case you want to keep your query, it should be enough to add a condition WHERElike this to filter:

    WHERE facturas.id IS NOT NULL;
    • 2
  2. Best Answer

    The core part of your question is "all cash movements" so you could do something like this:

    SELECT c.id, 
       c.fecha, 
       c.monto, 
       c.descripcion, 
       p.nombre, 
       pr.descripcion
       FROM caja c
       LEFT JOIN facturas f
            ON f.factura_id = c.factura_id
       LEFT JOIN proveedores p
            ON f.proveedor_id = p.proveedor_id
       LEFT JOIN productos pr
            ON f.producto_id = pr.producto_id

    Detail:

    • The fundamental thing here is that we start from cajaas the base table, and then we use LEFT JOINwith the rest of the tables, which ensures us all the rows of cajaand those that match from the rest of the tables.
    • We are using JOINSexplicits, take it as a good practice, in addition to indicating an alias to each table that will make your life easier
    • Obviously those rows caja that do not have factura_idwill produce NULLin the columns p.nombreand pr.descripcion.
    • 1