Link to home
Start Free TrialLog in
Avatar of Elroy Taulton
Elroy TaultonFlag for United States of America

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.

>>>>>>>>>>>>>>>>>>>>>>EXISTING 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_NAME, u.USR_LAST_NAME, doc.DOC_TYPE_ID, iwh.FINISH_TIME
ASKER CERTIFIED SOLUTION
Avatar of Jesus Rodriguez
Jesus Rodriguez
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial