CODEWITHSUNDEEP
sql-serverleft-join
Edgar Holguin
Edgar Holguin

Reputation: 187

SQL left join query with conditionals

I have this two tables from my program to manage stock:

Articulos (items):

id_articulo (id_item), nombre (name), tipo (kind)

Salidas (shipment):

id_articulo (id_item), cant_sale (shipment amount), imp_total (total cost), almacen_destino (shipemt stock destiny), date

I was asked to do a static list of products so they will always show the same items in the same order where the item kind (tipo) =something

when i want to do a search to an specific shipment stock desiny (almacen_destino) between dates i cant get all the items with the same kind (tipo) because they were not shipped

this is the query i have so far:

select a.id_articulo, sum(s.cant_sale) as Cant_sale, sum(s.imp_total) as Imp_total
from articulos a left join salidas s
on a.id_articulo=s.id_articulo
WHERE a.tipo='EMPAQUES'
and FECHA BETWEEN '06/06/2012' AND '12/06/2012'
and s.almacen_destino like 'CERRO COLORADO'
group by a.id_articulo

It shows only the shipped items to that stock destiny where tipo (kind)=Empaques between those dates, ill like to show the same information plus all the other items with the same kind (tipo) that where not shipped even if shipment amount and total cost is null.

In advance, Thank you.

Upvotes: 0

Views: 316

Answers (2)

Aaron Bertrand
Aaron Bertrand

Reputation: 280252

Putting filter criteria on the outer table converts your outer join to an inner join. Did you try:

SELECT 
  a.id_articulo, 
  SUM(s.cant_sale) AS Cant_sale, 
  SUM(s.imp_total) AS Imp_total
FROM
  dbo.articulos AS a 
LEFT OUTER JOIN
  dbo.salidas AS s
  ON a.id_articulo = s.id_articulo
  AND s.almacen_destino = 'CERRO COLORADO'
  AND s.FECHA BETWEEN '20120606' AND '20120612'
WHERE a.tipo = 'EMPAQUES'
GROUP BY a.id_articulo;

I guessed on a few things here:

  • FECHA is in salidas - if not, then move that AND to the WHERE clause.
  • your dates are d/m/y. Please always use unambiguous date formats - you may be in the UK or Canada, but not everyone in your audience is, and we are not able to read your mind.

Upvotes: 3

bluevector
bluevector

Reputation: 3493

You have to include any WHERE clause chunks that have to with the LEFT JOINed table in the ON clause.

Upvotes: 1

Related Questions