title: Oracle single query with multiple lookups to same table
body: <p>I have a reference table containing a list of account numbers. For each account in that table, I need to query a table containing a list of activities; each activity can have zero to many associated notes. Each activity also has a product, call type, reason and outcome, which are stored as references to a category table containing these in a single field. And just for fun, I have to pull all of this data in a single query.</p>
  
  <p>Here's where I am so far:</p>
  
  <pre><code>SELECT 
      ACTIVITY.ACTIVITYID, 
      ACTIVITY.ACCOUNTNO, 
      ACTIVITY.CREATEDATETIME, 
      C1.CATDESC AS PRODUCT, 
      C2.CATDESC AS CALLDRIVER, 
      C3.CATDESC AS REASON, 
      C4.CATDESC AS OUTCOME, 
      NOTE.NOTEID, 
      NOTE.NOTEDESC 
  FROM NOTE 
  RIGHT JOIN ACTIVITY 
      ON NOTE.ACTIVITYID = ACTIVITY.ACTIVITYID 
  RIGHT JOIN REFERENCE 
      ON ACTIVITY.ACCOUNTNO = REFERENCE.ACCOUNTNO 
  INNER JOIN CATEGORY C1 
      ON ACTIVITY.PRODUCTCODE = C1.CATCODE 
  INNER JOIN CATEGORY C2 
      ON ACTIVITY.CALLDRIVERCODE = C2.CATCODE 
  INNER JOIN CATEGORY C3 
      ON ACTIVITY.REASONCODE = C3.CATCODE 
  INNER JOIN CATEGORY C4 
      ON ACTIVITY.OUTCOMECODE = C4.SOURCECATCODE
  WHERE ACTIVITY.CREATEDATETIME &gt;= (SYSDATE -30)
  ORDER BY ACTIVITYID, NOTEID
  </code></pre>
  
  <p>This almost does what I want it to do, except that it returns many, many more rows than it should (between 12 and 40 rows instead of 1-3). If I remove the joins to <code>CATEGORY</code>, like so:</p>
  
  <pre><code>SELECT 
      ACTIVITY.ACTIVITYID, 
      ACTIVITY.ACCOUNTNO, 
      ACTIVITY.CREATEDATETIME, 
      NOTE.NOTEID, 
      NOTE.NOTEDESC 
  FROM NOTE 
  RIGHT JOIN ACTIVITY 
      ON NOTE.ACTIVITYID = ACTIVITY.ACTIVITYID 
  RIGHT JOIN REFERENCE 
      ON ACTIVITY.ACCOUNTNO = REFERENCE.ACCOUNTNO 
  WHERE ACTIVITY.CREATEDATETIME &gt;= (SYSDATE -30)
  ORDER BY ACTIVITYID, NOTEID
  </code></pre>
  
  <p>then it works perfectly as expected, so I know the problem is with the repeated joins to the <code>CATEGORY</code> table.</p>
  
  <p>The <code>PRODUCTCODE</code>, <code>CALLDRIVERCODE</code>, <code>REASONCODE</code> and <code>OUTCOMECODE</code> fields in <code>ACTIVITY</code> all map to the <code>CATCODE</code> (id) and <code>CATDESC</code> (string) fields in <code>CATEGORY</code>. What I'm looking for is an alternative way to look up these values on a row by row basis, while still containing all of this within a single query.</p>