combine query

Question

combine query

coool_sweet

SSCommitted

Points: 1892
More actions
July 24, 2017 at 6:57 pm

#327675

hi
i have table like this
item
item no , itemdesc open date closedate eventdate
1 reject 01/01/2016 01/02/2017 03/04/2009
2 approve 03/02/2013 03/04/2008 02/02/2012
3 in progress 03/02/2014 04/05/2016 06/05/2013
itemcode
1 abs
2 edf
3 esd
now here what i am doing is
select itemno,item code,closedate
from item a join itemcode b
on a.itemno= b.itemno
where a.itemdesc ='aprove'
select itemno,item code,opendate
from item a join itemcode b
on a.itemno= b.itemno
where a.itemdesc ='inprogress '
select itemno,item code,eventdate
from item a join itemcode b
on a.itemno= b.itemno
where a.itemdesc ='reject'
so based on itemdesc i am choosing different field,how will i combine everything in 1 query?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63503 More actions · Answer 1

Use OR/IN in the your WHERE clause.

SELECT itemno,item code,closedate FROM item a INNER JOIN itemcode bFROM item a INNER JOIN itemcode b ON a.itemno= b.itemnoON a.itemno= b.itemno WHERE a.itemdesc IN *('approve', 'in progress', 'received');WHERE a.itemdesc IN *('approve', 'in progress', 'received');

coool_sweet SSCommitted Points: 1892 More actions · Answer 2

I want something liekt his

SELECT itemno,item code,closedate as new_date(in progress),close_date for open_date(approve)
FROM item a INNER JOIN itemcode bFROM item a INNER JOIN itemcode b
ON a.itemno= b.itemnoON a.itemno= b.itemno
WHERE  a.itemdesc IN *('approve', 'in progress', 'received');WHERE  a.itemdesc IN *('approve', 'in progress', 'received');

pietlinden SSC Guru Points: 63503 More actions · Answer 3

Why not make things easy for everyone and tell us all what your expected output is given the data you have already provided?

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 4

maybe something like:
SELECT a.itemno, b.itemcode, CASE a.itemdesc WHEN 'aprove' THEN a.closedate WHEN 'inprogress' THEN a.opendate WHEN 'reject' THEN a.eventdate END AS evemtdate FROM item a INNER JOIN itemcode b ON a.itemno = b.itemno WHERE a.itemdesc IN ('aprove', 'inprogress', 'reject')