Oracle joins (left outer, right, etc .: S)

Question

I knew that stackoverflow would help me, besides finding out what a "favorite software cartoon" is: P

That was the accepted answer: Bill Carvin

Thank you all for your help (I would like to vote twice for you)

My request turned out like this (this is real)

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; 

Now I just need to join the other tables for a readable report.

---

ORIGINAL MAIL

Hello.

I'm afraid about 6 hours. now with a DB request (my long-term enemy)

I have a data table with fields such as:

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

Used to track ticket history

Identifier : Customer Identifier (NAF0000001)

activity : this is fk where is the ticket (new, in_progress, rejected, closed, etc.)

participant : is fk of the person who is visiting the ticket at that moment

closedate : this is the completion date of this operation.

EDIT: I should have said “completion,” not closing. This is the date the action was completed, not necessarily when the ticket was closed.

For example, a typical story could be:

  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 party 1 = jonh, 2 = scott, 3 = mike, 4 = rob

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

etc .. And dozens of other irrelevant information.

Well, my problem is as follows.

I managed to create a request where I can find out when the ticket was open and closed

This is true:

  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 

But I don’t know which tickets are not closed and who visits them.

So far, I have something like this:

  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 

This gives me everyone who has a beginning (new = 1) but not closed (closed = 4)

But the big problem is that he prints the member who opened the ticket, but I need the member who visits him. Therefore, I am adding inprogress activity to the request.

  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.. 

But not all the lines that are in the "new" are "inprogress", and with this request I throw them all.

I need to show all inprogress participants, and if the ticket is not inprogress, it will be displayed as 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 this case

NA002, NA003 and NA004 are in the "new", so no members are displayed

While

NA005 and NA006 are "inprgress (act = 2)" and rob (member 4) is participating

So, I remember that this thing was called a left outer join or something like that, but I never understand it. I would like to know how I can get identifiers that are "inprogress" and "new" and which are not closed.

Probably having a little rest, I can clear my mind. If anyone knows how to do this, I would appreciate it.

By the way, I tried:

  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.. 

But it gives the same result as the previous one (discards only in "new" records)

Answer 1

Try something like this (I have not tested it):

 SELECT p_new.identifier, COALESCE(p_inprog.activity, p_new.activity) AS activity, p_inprog.participant, COALESCE(p_inprog.closedate, p_new.closedate) AS closedate FROM performance p_new LEFT OUTER JOIN performance p_inprog ON (p_new.identifier = p_inprog.identifier AND p_inprog.activity = 2) LEFT OUTER JOIN performance p_closed ON (p_new.identifier = p_closed.identifier AND p_closed.activity = 4) WHERE p_new.activity = 1 AND p_closed.identifier IS NULL; 

I think that people think that external connections are more complicated than they really are. For instance:

 A LEFT OUTER JOIN B ON (...condition...) 

This returns all rows from A, regardless of whether there are matching rows in B. If none of the rows in B matches, treat all columns of B. * as NULL in the result set for this row A. The join condition can be an expression which must match the string in B, or it is not included in the union. Thus, more lines in will be solo.

Answer 2

Generally, the best way to write them is with EXISTS. The first one is:

 select * from performance p1 where not exists ( select * from performance p2 where p2.identifier = p1.identifier and p2.activity = 4 ) 

This method allows you to search by keywords in the performance.identifier file, rather than potentially creating a massive list of identifiers in (select identifier from performance where activity=4) .

Answer 3

I think this should do it.

The first part gets all the records that are new, not closed and not executed. The second part receives all reports on the work done. Then we combine them together, we can also sort by ID by wrapping "SELECT * FROM" around this query.

 select a.identifier, a.participant, a.closedate as start from performance a where a.activity = 1 and not exists ( select identifier from performance b where b.activity = 4 and b.identifier = a.identifier) and not exists ( select identifier from performance c where c.activity = 2 and c.identifier = a.identifier) UNION ALL select a.identifier, a.participant, a.closedate as start from performance a where a.activity = 2 and not exists ( select identifier from performance b where b.activity = 4 and b.identifier = a.identifier); 

Answer 4

I would suggest that you need the earliest record (presumably, but not necessarily the one with activity = 1) and the most recent record (regardless of the activity number). If the activity of the last record is 4, the ticket is closed. otherwise, the participant is the current ticket holder. There is a potential error introduced by matching activity = 4 if the ticket can be reopened.

Actually, based on your example, you might not even need the earliest recording. How about the following:

 SELECT identifier, activity, participant, closedate FROM performance a WHERE (a.identifier, a.closedate) in (select b.identifier, max(b.closedate) from performance b group by b.identifier ) ; 

Answer 5

How about this:

 SELECT * FROM ( SELECT identifier, MAX(activity) activity, MAX(participant) KEEP (DENSE_RANK LAST ORDER BY activity) FROM performance GROUP BY identifier ) WHERE activity in (1,2) 

An internal query gives the latest activity for each ticket and its corresponding member. An external request filters this to those where the activity is either "new" or "in progress."

I like the functions of DENSE_RANK.

Answer 6

Firstly, you may have a design problem if you can open a client with multiple tickets at the same time. Ideally, you should have ticket_id, and then you can execute the Andy request using ticket_id instead of an identifier.

Answer 7

Which tickets are not closed:

 select identifier as closed_identifier from performance where identifier not exists (select identifier from performance where activity=4) 

Tickets that attend:

 select identifier as inprogress_identifier, participant performance from performance where activity=2 

Non-closed tickets with participation of a participant:

 select * from (select identifier as notclosed_identifier from performance where identifier not exists (select identifier from performance where activity=4)) closed left join (select identifier as inprogress_identifier, participant performance from performance where activity=2) attended on notclosed_identifier=inprogress_identifier 

Answer 8

Maybe you can use this type of request as a starting point.

 select x.identifier, max(x.p_1) as new_participant, max(x.c_1) as new_date, max(x.p_2) as inprogress_participant, max(x.c_2) as inprogress_date, max(x.p_3) as approval_participant, max(x.c_3) as approval_date, max(x.p_4) as closing_participant, max(x.c_4) as closing_date from ( select a.identifier, decode (activity, 1, participant, null) as p_1, decode (activity, 1, closedate, null) as c_1, decode (activity, 2, participant, null) as p_2, decode (activity, 2, closedate, null) as c_2, decode (activity, 3, participant, null) as p_3, decode (activity, 3, closedate, null) as c_3, decode (activity, 4, participant, null) as p_4, decode (activity, 4, closedate, null) as c_4 from performance a ) x group by x.identifier 

The idea is to serialize a table from a row into a field and create a view based on it. You can create a report based on this view.

Hello,

Answer 9

Just a quick idea that others can build on (untested, but I hope this idea came across):

First, select the still unclosed actions (as published by others):

 select id from performance p1 where identifier not exists (select * from performance p2 where activity=4 and p1.id=p2.id) 

You can then add the person participating in this event by adding a subquery in the select clause:

 select id, (select participant from performance p3 where p3.activity=3 and p1.id=p2.id) from performance p1 where identifier not exists (select * from performance p2 where activity=4 and p1.id=p2.id) 

If there is no 3rd event record for this id, the subquery returns zero, which is exactly what we need.

We hope this helps - please turn around if necessary.