CODEWITHSUNDEEP
sqloracle-databasejoin
Joaquin Gavilan Rojas
Joaquin Gavilan Rojas

Reputation: 21

My left join in oracle sql is not returning every element of the left table

i'm trying to get all the elements on the table B_ARTICULOS and make some calculations joining other tables (where some elements of B_ARTICULOS are not present) and I know I have to use a left join for that but I dont know what i'm doing wrong.

With this query I dont get every B_ARTICULOS, only those that are listed on the other tables

SELECT a.id, a.nombre,
  CASE WHEN a.id IN dc.id_articulo THEN dc.cantidad ELSE 0 END CANTIDAD_COMPRAS,
  CASE WHEN a.id IN dc.id_articulo THEN dc.cantidad * a.costo ELSE 0 END MONTO_COMPRAS, 
  CASE WHEN a.id IN dv.id_articulo THEN dv.cantidad ELSE 0 END CANTIDAD_VENTAS, 
  CASE WHEN a.id IN dv.id_articulo THEN dv.cantidad * a.precio ELSE 0 END MONTO_VENTAS 
FROM B_ARTICULOS a
  LEFT JOIN B_DETALLE_COMPRAS dc ON a.id = dc.id_articulo
  JOIN B_COMPRAS c ON  dc.id_compra = c.id
  JOIN B_DETALLE_VENTAS dv ON dv.id_articulo = a.id
  JOIN B_VENTAS v ON v.id = dv.id_venta
WHERE a.id IS NOT NULL;

Upvotes: 1

Views: 79

Answers (2)

Junjie
Junjie

Reputation: 521

You have to use left join too for other tables, like so:

...
FROM B_ARTICULOS a
  LEFT JOIN B_DETALLE_COMPRAS dc ON a.id = dc.id_articulo
  LEFT JOIN B_COMPRAS c ON  dc.id_compra = c.id
  LEFT JOIN B_DETALLE_VENTAS dv ON dv.id_articulo = a.id
  LEFT JOIN B_VENTAS v ON v.id = dv.id_venta
...

Personally, I make subqueries for all the related tables that will be left joined to the main table. I grouped those tables then left join them to the main, like so:

...
FROM B_ARTICULOS a
  LEFT JOIN (
    SELECT dc.*
    FROM B_DETALLE_COMPRAS dc
      JOIN B_COMPRAS c ON dc.id_compra = c.id
  ) dc on a.id = dc.id_articulo
  LEFT JOIN (
    SELECT dv.*
    FROM B_DETALLE_VENTAS dv
      JOIN B_VENTAS v ON v.id = dv.id_venta
  ) dv ON dv.id_articulo = a.id
...

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520878

The only explanation which I can see here is that you should be left joining to some of those other tables, beyond the second B_DETALLE_COMPRAS table. Assuming you would use left joins everywhere:

SELECT
    a.id,
    a.nombre,
    CASE WHEN a.id IN dc.id_articulo THEN dc.cantidad ELSE 0 END CANTIDAD_COMPRAS,
    CASE WHEN a.id IN dc.id_articulo THEN dc.cantidad * a.costo ELSE 0 END MONTO_COMPRAS, 
    CASE WHEN a.id IN dv.id_articulo THEN dv.cantidad ELSE 0 END CANTIDAD_VENTAS, 
    CASE WHEN a.id IN dv.id_articulo THEN dv.cantidad * a.precio ELSE 0 END MONTO_VENTAS 
FROM B_ARTICULOS a
LEFT JOIN B_DETALLE_COMPRAS dc ON a.id = dc.id_articulo
LEFT JOIN B_COMPRAS c ON dc.id_compra = c.id
LEFT JOIN B_DETALLE_VENTAS dv ON dv.id_articulo = a.id
LEFT JOIN B_VENTAS v ON v.id = dv.id_venta
WHERE a.id IS NOT NULL;

Upvotes: 1

Related Questions