CODEWITHSUNDEEP
sqloracle-databasejoin
Toby Nieboer
Toby Nieboer

Reputation: 86

Oracle single query with multiple lookups to same table

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.

Here's where I am so far:

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 >= (SYSDATE -30)
ORDER BY ACTIVITYID, NOTEID

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 CATEGORY, like so:

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 >= (SYSDATE -30)
ORDER BY ACTIVITYID, NOTEID

then it works perfectly as expected, so I know the problem is with the repeated joins to the CATEGORY table.

The PRODUCTCODE, CALLDRIVERCODE, REASONCODE and OUTCOMECODE fields in ACTIVITY all map to the CATCODE (id) and CATDESC (string) fields in CATEGORY. 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.

Upvotes: 1

Views: 3353

Answers (1)

Rene
Rene

Reputation: 10541

As an alternative you can put the category queries in the select part of the query.

select ...
  ,(select c1.catdesc from category c1
    where c1.catcode=activity.productcode)  as product
  ,(select c2.catdesc from category c2
    where c2.catcode=activity.calldrivercode)  as calldriver
...

Upvotes: 1

Related Questions