Databases
StackOverflow2013
Postsdbo
POOracle joins ( left outer, right, etc. :S )
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POOracle joins ( left outer, right, etc. :S )
    primarykey
    data
    text
    <p>I knew stackoverflow would help me for other than know what is the "favorite programming cartoon" :P </p> <p>This was the accepted answer by: <a href="https://stackoverflow.com/questions/185327/oracle-joins-left-outer-right-etc-s#185439">Bill Karwin</a></p> <p>Thanks to all for the help ( I would like to double vote you all ) </p> <p>My query ended up like this ( this is the real one ) </p> <pre><code>SELECT accepted.folio, COALESCE( inprog.activityin, accepted.activityin ) as activityin, inprog.participantin, accepted.completiondate FROM performance accepted LEFT OUTER JOIN performance inprog ON( accepted.folio = inprog.folio AND inprog.ACTIVITYIN IN ( 4, 435 ) -- both are ids for inprogress AND inprog.PARTICIPANTIN != 1 ) -- Ignore the "bot" participant LEFT OUTER JOIN performance closed ON( accepted.folio = closed.folio AND closed.ACTIVITYIN IN ( 10,436, 4, 430 ) ) -- all these are closed or cancelled WHERE accepted.ACTIVITYIN IN ( 3, 429 ) --- both are id for new AND accepted.folio IS NOT NULL AND closed.folio IS NULL; </code></pre> <p>Now I just have to join with the other tables for a human readable report.</p> <p><hr> <strong>ORIGINAL POST</strong></p> <p>Hello. </p> <p>I'm struggling for about 6 hrs. now with a DB query ( my long time nemesis ) </p> <p>I have a data table with some fields like:</p> <pre><code>table performance( identifier varchar, activity number, participant number, closedate date, ) </code></pre> <p>It is used to keep track of the history of ticket</p> <p><strong>Identifier</strong>: is a customer id like ( NAF0000001 ) </p> <p><strong>activity</strong>: is a fk of where the ticket is ( new, in_progress, rejected, closed, etc )</p> <p><strong>participant</strong>: is a fk of who is attending at that point the ticket</p> <p><strong>closedate</strong>: is the date when that activity finished.</p> <p><strong>EDIT:</strong> I should have said "completiondate" rather than closedate. This is the date when the activity was completed, not necessary when the ticket was closed.</p> <p>For instance a typical history may be like this:</p> <pre> 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| ------------------------------------------- </pre> <p>And participant 1=jonh, 2=scott, 3=mike, 4=rob</p> <p>and activties 1=new, 2=inprogress, 3=waitingforapproval, 4=closed</p> <p>etc. And tens of other irrelevant info.</p> <p>Well my problem is the following.</p> <p>I have managed to create a query where I can know when a ticket was opened and closed</p> <p>it is like this:</p> <pre><code> select a.identifier, a.participant, a.closedate as start, b.closedate as finish from performance a, performance b where a.activity = 1 -- new and b.activity = 4 -- closed and a.identifier = b.identifier </code></pre> <p>But I can't know what tickets are <strong>not</strong> closed and who is attending them.</p> <p>So far I have something like this:</p> <pre><code> select a.identifier, a.participant, a.closedate as start from performance a where a.activity = 1 -- new and a.identifier not in ( select identifier from performance where activity = 4 ) --closed </code></pre> <p>That is give me all the ones who have an start ( new = 1 ) but are not closed ( closed = 4 ) </p> <p>But the big problem here is that it prints the participant who opened the ticket, but I need the participant who is attending it. So I add the "inprogress" activity to the query.</p> <pre><code> select a.identifier, a.participant, a.closedate as start from performance a, performance b where a.activity = 1 -- new and a.identifier not in ( select identifier from performance where activity = 4 ) --closed and b.identifier = a.identifier and b.activity = 2 -- inprogress.. </code></pre> <p>But not all the rows that are in "new" are "inprogress" and with that query I drop all of them.</p> <p>What I need is to show all the "inprogress" participant and if the ticket is not "inprogress", it will show as empty.</p> <p>Somthing like</p> <pre> 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| </pre> <p>In this case</p> <p>NA002, NA003 and NA004 are in "new", so no participant is shown</p> <p>While</p> <p>NA005 and NA006 are being "inprgress (act = 2 )" and they are being attended by rob ( participant 4 ) </p> <p>So I remember there was this thing called left outer join or something like that but I never ever understand it. What I would like to know is how can I fetch the identifiers that are "inprogress" and "new" and that are not closed.</p> <p>Probably taking a little rest would help me to clear my mind. If anyone knows how to do it I'll appreciate it.</p> <p>By the way I've tried:</p> <pre><code> select a.identifier, a.participant, a.closedate as start from performance a left outer join performance b on b.identifier = a.identifier where a.activity = 1 -- new and a.identifier not in ( select identifier from performance where activity = 4 ) --closed and b.activity = 2 -- inprogress.. </code></pre> <p>But gives me the same result as the previous ( drop the only in "new" records )</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USOscarRyz
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POOracle joins ( left outer, right, etc. :S )
      1. USAndy Lester
      1. VTFavorite
    2. VO
      singulars
      1. POOracle joins ( left outer, right, etc. :S )
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POOracle joins ( left outer, right, etc. :S )
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload