[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How to INNER JOIN an additional Table & Column

Posted on 2011-10-20
1
Medium Priority
?
304 Views
Last Modified: 2012-06-21
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
0
Comment
Question by:Elroy Taulton
1 Comment
 
LVL 13

Accepted Solution

by:
Jesus Rodriguez earned 2000 total points
ID: 37000757
Check the Query on the attach file
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,

       ZDOC.DOC_TYPE_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

       INNER JOIN DOC_TYPE as ZDOC
          on ZDOC.DOC_TYPE_ID = IN_DOC.DOC_TYPE_ID


 WHERE i.ITEM_TYPE = 1 AND i.QUEUE_ID = '2000000BG7_00031H3LD522'
       and u.USR_NAME <> 'workflow.agent_001' 
 group by ZDOC.DOC_TYPE_NAME,i.item_id,i.COMPLETE_TIME, u.USR_NAME,u.USR_FIRST_NAME, u.USR_LAST_NAME, doc.DOC_TYPE_ID, iwh.FINISH_TIME

Open in new window

0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question