title: ORACLE SQL Query regarding joining to a join
body: <p>I have the following problem.  I am currently trying to return information from a number of different tables in our database. The process is that an activity is added to the system, which should be theoretically followed by an agreement of a certain type.  However, sometimes the activity is not followed by the expected agreement, and I would like to identify where this is the case.</p>
  
  <p>I currently have a query (shown below) which returns data which looks like this (simplified from the actual query):</p>
  
  <pre><code>ID  Activity  Agreement     Agreement Type
  1   X         Budgets       Payment
  2   X
  3   X         Budgets
  </code></pre>
  
  <p>What the data should look like follows:</p>
  
  <pre><code>ID  Activity  Agreement     Agreement Type
  1   X         Budgets       Payment
  2   X
  3   X         
  </code></pre>
  
  <p>Basically, if there is no agreement type of payment, I don't want the agreement to show (but I still want to see the activity).  ID is used to join activity to agreements, but agreements and agreement type are from seperate tables joined on a description ID.</p>
  
  <pre><code>SELECT  O_ACTIVITIES.ACT_SUBJECT_ID as "ID",
          initcap(olm_bo.get_per_name(O_ACTIVITIES.ACT_SUBJECT_ID)) as "Name",
          O_ACTIVITIES.ACT_ID as "Activity ID",
          initcap(O_ACTIVITY_TYPES.ACT_DESC) as "Activity Type",
          O_ACTIVITIES.ACT_REQUESTED_DATE as "Start Date",
          case when olm_bo.get_ref_desc(O_ACTIVITIES.ACT_STATUS,'ACTIVITY_STATUS') = 'Newly generated' 
                    then null 
                    else O_ACTIVITIES.ACT_STATUS_DATE 
               end as "End Date",
          olm_bo.get_ref_desc(O_ACTIVITIES.ACT_STATUS,'ACTIVITY_STATUS') as "Status",
          O_ACTIVITIES.ACT_CREATED_BY as "Created by",
          O_AGREEMENT_DETAILS.ADE_ID as "Agreement ID",
          initcap(olm_bo.get_sty_name(O_AGREEMENT_DETAILS.ADE_STY_ID)) as "Service Type",
          initcap(olm_bo.get_sty_name(O_SERVICE_ELEMENTS.SEL_STY_CHILD_ID)) as "Service Element",
          O_AGREEMENT_DETAILS.ADE_START_DATE as "Agreement Start",
          O_AGREEMENT_DETAILS.ADE_END_DATE as "Agreement End",
          O_AGREEMENT_DETAILS.ADE_ENTERED_BY as "Entered by"
  FROM O_ACTIVITIES
  LEFT JOIN O_ACTIVITY_TYPES 
         ON O_ACTIVITY_TYPES.ACT_CLASS= O_ACTIVITIES.ACT_CLASS and 
            O_ACTIVITY_TYPES.ACT_TYPE=O_ACTIVITIES.ACT_TYPE AND
            initcap(O_ACTIVITY_TYPES.ACT_DESC)  =  'X'
  LEFT OUTER JOIN O_AGREEMENT_DETAILS 
         ON O_AGREEMENT_DETAILS.ADE_SUBJECT_ID=O_ACTIVITIES.ACT_SUBJECT_ID  AND 
            initcap(olm_bo.get_sty_name(O_AGREEMENT_DETAILS.ADE_STY_ID)) IN ('Budgets') AND 
            O_AGREEMENT_DETAILS.ADE_START_DATE &gt;= O_ACTIVITIES.ACT_REQUESTED_DATE
  LEFT JOIN O_SERVICE_ELEMENTS 
         ON O_AGREEMENT_DETAILS.ADE_SEL_ID=O_SERVICE_ELEMENTS.SEL_ID AND
            initcap(olm_bo.get_sty_name(O_SERVICE_ELEMENTS.SEL_STY_CHILD_ID)) IN ('Payment')
  WHERE (O_ACTIVITIES.ACT_SYSTEM_IND IS NULL and 
        NVL(O_ACTIVITIES.ACT_REC_TYPE,'???') NOT IN ('Y')  ) AND
        initcap(O_ACTIVITY_TYPES.ACT_DESC)  =  'X'
  </code></pre>
  
  <p>The problem is that the service element table which contains the agreement type description is seperate from the agreements table.  I could use the agreement type code (which actually joins the agreement type to the agreement table), however, really I just want to know if I can achieve an inner join between the agreement table and the agreement type table which doesn't impact the activity table.  </p>
  
  <p>Any pointers and advice are appreciated, hopefully I've explained myself sufficiently.   </p>