Elroy Taulton
asked on
How to INNER JOIN an additional Table & Column
I have the code below to retrieve some values out of a system. I need to add an additional table to this and need some assistance adding it. The information that needs to be added is:
TABLE NAME: DOC_TYPE
COLUMN NAME: DOC_TYPE_NAME
DOC_TYPE.DOC_TYPE_ID = IN_DOC.DOC_TYPE_ID
Need to add DOC_TYPE_NAME to GROUP BY list and SELECT list.
>>>>>>>>>>>>>>>>>>>>>>EXIS TING CODE>>>>>>>>>>>>>>>>>>>>>> >>>>>>>>>> >>>
SELECT i.ITEM_ID,
i.COMPLETE_TIME,
iwh.FINISH_TIME,
doc.DOC_TYPE_ID,
u.USR_NAME, u.USR_FIRST_NAME, u.USR_LAST_NAME,
count(i.item_id)as item_count
FROM IN_WF_ITEM i
INNER JOIN IN_WF_ITEM_QUEUE_HIST iwh
ON i.ITEM_ID = iwh.ITEM_ID AND iwh.QUEUE_ID IN
('301YTCF_006X864V3008KJ6' , '301YTCF_006X8B4V3008LFK')
INNER JOIN IN_WF_ITEM_QUEUE_HIST iwh2
ON iwh.ITEM_ID = iwh2.ITEM_ID
and iwh.SEQ_NUM = iwh2.SEQ_NUM-1
and iwh2.QUEUE_ID = '2000000BG7_00031H3LD522'
INNER JOIN IN_DOC doc
ON i.OBJ_ID = doc.DOC_ID
INNER JOIN IN_SC_USR u
ON u.USR_ID = iwh.START_USR_ID
WHERE i.ITEM_TYPE = 1 AND i.QUEUE_ID = '2000000BG7_00031H3LD522'
and u.USR_NAME <> 'workflow.agent_001' group by i.item_id,i.COMPLETE_TIME, u.USR_NAME,u.USR_FIRST_NAM E, u.USR_LAST_NAME, doc.DOC_TYPE_ID, iwh.FINISH_TIME
TABLE NAME: DOC_TYPE
COLUMN NAME: DOC_TYPE_NAME
DOC_TYPE.DOC_TYPE_ID = IN_DOC.DOC_TYPE_ID
Need to add DOC_TYPE_NAME to GROUP BY list and SELECT list.
>>>>>>>>>>>>>>>>>>>>>>EXIS
SELECT i.ITEM_ID,
i.COMPLETE_TIME,
iwh.FINISH_TIME,
doc.DOC_TYPE_ID,
u.USR_NAME, u.USR_FIRST_NAME, u.USR_LAST_NAME,
count(i.item_id)as item_count
FROM IN_WF_ITEM i
INNER JOIN IN_WF_ITEM_QUEUE_HIST iwh
ON i.ITEM_ID = iwh.ITEM_ID AND iwh.QUEUE_ID IN
('301YTCF_006X864V3008KJ6'
INNER JOIN IN_WF_ITEM_QUEUE_HIST iwh2
ON iwh.ITEM_ID = iwh2.ITEM_ID
and iwh.SEQ_NUM = iwh2.SEQ_NUM-1
and iwh2.QUEUE_ID = '2000000BG7_00031H3LD522'
INNER JOIN IN_DOC doc
ON i.OBJ_ID = doc.DOC_ID
INNER JOIN IN_SC_USR u
ON u.USR_ID = iwh.START_USR_ID
WHERE i.ITEM_TYPE = 1 AND i.QUEUE_ID = '2000000BG7_00031H3LD522'
and u.USR_NAME <> 'workflow.agent_001' group by i.item_id,i.COMPLETE_TIME,
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.