oracle joins ( left outer, right, etc. :s )

i knew assistance me know "favorite programming cartoon" :p

this supposed answer by:

thanks assistance ( i double opinion )

my query finished adult ( genuine )

select 
    accepted.folio,
    coalesce( inprog.activityin, accepted.activityin ) activityin,
    inprog.participantin,
    accepted.completiondate 
from opening accepted
    left outdoor join opening inprog 
        on( accepted.folio = inprog.folio 
            inprog.activityin 
            ( 4, 435 )                    -- both ids inprogress
            inprog.participantin != 1  ) -- replace "bot" participant
    left outdoor join opening closed
        on( accepted.folio = closed.folio 
            closed.activityin ( 10,436, 4, 430  ) )  -- closed cancelled
where accepted.activityin ( 3, 429 )      --- both id new 
and accepted.folio null
and closed.folio null;

now i only have join tables tellurian entertaining report.

---

original post

hello.

i'm struggling 6 hrs. db query ( enlarged nemesis )

i have information list fields like:

table performance( 
     identifier varchar, 
     activity    number, 
     member number, 
     closedate   date, 
)

it used keep lane story ticket

identifier: fan id ( naf0000001 )

activity: fk where sheet ( new, in_progress, rejected, closed, etc )

participant: fk attending during indicate ticket

closedate: date activity finished.

edit: i should have conspicuous "completiondate" rather closedate. date activity completed, required sheet closed.

for instance customary story competence this:

identifier|activity|participant|closedate
-------------------------------------------
na00000001|       1|          1|2008/10/08 15:00|
-------------------------------------------
na00000001|       2|          2|2008/10/08 15:20|
-------------------------------------------
na00000001|       3|          2|2008/10/08 15:40|
-------------------------------------------
na00000001|       4|         4|2008/10/08 17:05|
-------------------------------------------

and member 1=jonh, 2=scott, 3=mike, 4=rob

and activties 1=new, 2=inprogress, 3=waitingforapproval, 4=closed

etc. tens irrelevant info.

well problem following.

i have managed emanate query where i know sheet uninterrupted closed

it this:

 name 
     a.identifier,
     a.participant,
     a.closedate start,
     b.closedate finish      
from 
    opening a,
    opening b
where
    a.activity = 1 -- new
    b.activity = 4 -- closed
    a.identifier = b.identifier

but i can't know tickets not closed attending them.

so distant i have something this:

 name 
     a.identifier,
     a.participant,
     a.closedate start
from 
    opening        
where
    a.activity = 1 -- new
    a.identifier ( name identifier opening where activity = 4 ) --closed

that give me ones have an start ( new = 1 ) nonetheless closed ( closed = 4 )

but large problem here prints member uninterrupted ticket, nonetheless i need member attending it. i supplement "inprogress" activity query.

 name 
     a.identifier,
     a.participant,
     a.closedate start
from 
    opening a,
    opening b        
where
    a.activity = 1 -- new        
    a.identifier ( name identifier opening where activity = 4 ) --closed
    b.identifier = a.identifier
    b.activity = 2  -- inprogress..

but rows "new" "inprogress" query i dump them.

what i need arrangement "inprogress" member sheet "inprogress", arrangement empty.

somthing like

    identifier|activity|participant|closedate
-------------------------------------------
na00000002|       1|           |2008/10/08 15:00|
-------------------------------------------
na00000003|       1|           |2008/10/08 15:20|
-------------------------------------------
na00000004|       1|           |2008/10/08 15:40|
-------------------------------------------
na00000005|       2|          4|2008/10/08 15:40|
-------------------------------------------
na00000006|       2|          4|2008/10/08 15:40|

in case

na002, na003 na004 "new", member shown

while

na005 na006 being "inprgress (act = 2 )" being attended sack ( member 4 )

so i remember there thing called left outdoor join something nonetheless i never ever know it. i know i fetch identifiers "inprogress" "new" closed.

probably holding small rest assistance me pristine mind. anyone knows i'll conclude it.

by proceed i've tried:

 name 
     a.identifier,
     a.participant,
     a.closedate start
from 
    opening a
    left outdoor join
    opening b  
         
    b.identifier = a.identifier
where
    a.activity = 1 -- new        
    a.identifier ( name identifier opening where activity = 4 ) --closed
    b.activity = 2  -- inprogress..

but gives me same outcome before ( dump wholly "new" annals )