CODEWITHSUNDEEP
sqloracle-databaseoracle11gself-join
Keith D
Keith D

Reputation: 29

Self-join in Oracle SQL - selecting rows based on 1 same column and 1 different column

It may not be a self-join I need, but I'm having difficulty with this problem and I hope someone can help me.

Sample Data:

Row   Code   ReservationID   ClientID EmployeeID
1     T            123          7777     John 
2     T            456          2020     John
3     P            456          2020     John
4     P            999          3100     Steve
5     P            876          3100     Steve
6     T            333          2424     John
7     P            333          2424     Lynda

Desired output:

Row   Code   ReservationID   ClientID EmployeeID
2     T            456          2020     John
3     P            456          2020     John
6     T            333          2424     John
7     P            333          2424     Lynda

I'm looking for a query that will only return Rows 2, 3, 6, and 7.

The selection criteria is when there is a row with Code = T and another row with Code = P but for the same Reservation ID and CLientID. The EmployeeID may be the same or different (I want to return the EmployeeID in the results but not use it in the select criteria).

Reading forums I think I need a self-join (which I don't know how to do). Any help you can offer would really be appreciated! Thanks!

Upvotes: 0

Views: 826

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270091

One method uses exists:

select t.*
from t
where (t.code = 'T' and exists (select 1 from t t2 where t2.reservationId = t.reservationId and t2.clientId = t.clientId and t2.code = 'P'
                               )
      ) or
      (t.code = 'P' and exists (select 1 from t t2 where t2.reservationId = t.reservationId and t2.clientId = t.clientId and t2.code = 'T'
                               )
      );

For performance, you want an index on (reservationId, clientId, code).

Upvotes: 3

The Impaler
The Impaler

Reputation: 48820

I think the simplest solution is:

select distinct t1.*
  from my_table t1
  join my_table t2 on t1.reservationid = t2.reservationid
  where t1.code = 'T' and t2.code = 'P'
     or t1.code = 'P' and t2.code = 'T';

Upvotes: 0

Mureinik
Mureinik

Reputation: 311573

I think a more convenient way would be to use the row_number window function. You'd give a row number for every row within the same ReservationID and ClientID in a descending order of Code, and then filter according to this numbering:

SELECT Row, Code, ReservationID, ClientID, EmployeeID
FROM   (SELECT Row, Code, ReservationID, ClientID, EmployeeID,
               ROW_NUMBER() OVER (PARTITION BY ReservationID, ClientID
                                  ORDER BY Code DESC) AS rn
        FROM   mytable) t
WHERE  rn = 1

Upvotes: 1

Related Questions