sql - Oracle single query with multiple lookups to same table -

i have reference table containing list of account numbers. each account in table, need query table containing list of activities; each activity can have 0 many associated notes. each activity has product, call type, reason , outcome, stored references category table containing these in single field. , fun, have pull of data in single query.

here's far:

select      activity.activityid,      activity.accountno,      activity.createdatetime,      c1.catdesc product,      c2.catdesc calldriver,      c3.catdesc reason,      c4.catdesc outcome,      note.noteid,      note.notedesc  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 activity.createdatetime >= (sysdate -30) order activityid, noteid 

this want do, except returns many, many more rows should (between 12 , 40 rows instead of 1-3). if remove joins category, so:

select      activity.activityid,      activity.accountno,      activity.createdatetime,      note.noteid,      note.notedesc  note  right join activity      on note.activityid = activity.activityid  right join reference      on activity.accountno = reference.accountno  activity.createdatetime >= (sysdate -30) order activityid, noteid 

then works expected, know problem repeated joins category table.

the productcode, calldrivercode, reasoncode , outcomecode fields in activity map catcode (id) , catdesc (string) fields in category. i'm looking alternative way these values on row row basis, while still containing of within single query.

as alternative can put category queries in select part of query.

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