SQL query, select values of a column only if

Question

Im using SLQ server 2005.

I have this table were i register a list of items creating an "order" wich will be sent to the warehouse so they know what it needs to be sent back.

this is my code.

id_pedido=id_order, id_articulo=id_item, descripcion=description, fecha=date, nombre=employee name, local=store, (inicia+entra+sale) Existencias=stocks, tipo=Kind, --estado=state, activo=active.--

select id, id_pedido, p.id_articulo, p.descripcion, p.fecha, u.nombre, p.local,a.inicia+a.entra-a.sale as Existencias, a.tipo
from pedidos p join usuarios u on p.id_usuario=u.id_usuario join articulos a on a.id_articulo=p.id_articulo
where estado='activo'

What i need is that in stocks column values are displayed only when the kind of the item is X. How can i show all rows with null in the stocks column when their kind is not X and the value when the kind is X?

This is because the client only want the employee to know the stock of those items but not of all the others.

score 3 · Accepted Answer · answered May 08 '13 at 02:51

3

You can use CASE to compare the the tipo column to the value needed:

select id, 
   id_pedido, 
   p.id_articulo, 
   p.descripcion, 
   p.fecha, 
   u.nombre, 
   p.local,
   case when a.tipo = 'X' then a.inicia+a.entra-a.sale end Existencias, 
   a.tipo
from pedidos p 
   join usuarios u on p.id_usuario=u.id_usuario 
   join articulos a on a.id_articulo=p.id_articulo
where estado='activo'

answered May 08 '13 at 02:51

sgeddes

62,311
6
61
83

Thank you so much!!!,I didn't know how to use case and when to use it, i love how every day we learn something new. – Edgar Holguin May 08 '13 at 02:55
@EdgarHolguin -- np, glad we could help! – sgeddes May 08 '13 at 03:05

score 0 · Answer 2 · answered May 08 '13 at 02:46

0

I would use a SQL Case statement that can be use to conditionally return values

answered May 08 '13 at 02:46

TGH

38,769
12
102
135

SQL query, select values of a column only if

2 Answers2