How to select both matching records on same table with same ID and Name

Question

I have encountered a bump when trying to execute the following

I have a table with entry and exit records of patients

My_table

ID     NAME       DATE       TYPE   PERFORMANCE_SCORE  
103    John       08/12/18    EX      8
103    John       08/04/18    EN      5
105    Dave       06/22/18    EX      12
105    Dave       06/14/18    EN      16
108    Ben        02/07/19    EX      15
108    Ben        02/01/19    EN      19

Where EN = Entry Record, EX = Exit record. I need to get both the entry and exit records where the difference in the 'Performance_Score' between 'Exit Record(EX)' and 'Entry Record(EN)' is negative. I am expecting final result to be like

Required Result:

ID     NAME       DATE       TYPE   PERFORMANCE_SCORE  
105    Dave       06/22/18    EX      12
105    Dave       06/14/18    EN      16
108    Ben        02/07/19    EX      15
108    Ben        02/01/19    EN      19

I have tried to do this with Union like below

select * from
(
SELECT
        a.ID,
        a.NAME,
        a.DATE,
        a.TYPE,
        a.PERFORMANCE_SCORE  
    FROM My_table a
    WHERE
        a.TYPE = 'EX' 

UNION ALL

    SELECT
        b.ID,
        b.NAME,
        b.DATE,
        b.TYPE,
        b.PERFORMANCE_SCORE  
    FROM My_table b
    WHERE
        b.TYPE = 'EN'
)
where a.ID = b.ID and a.NAME = b.NAME and b.PERFORMANCE_SCORE - a.PERFORMANCE_SCORE < 0  --> I know this condition can't be used outside the UNION block, but how can I implement this condition to get the result I need?

When I try to use INNER JOIN like this

SELECT * FROM
(
    SELECT
        ID,
        NAME,
        DATE,
        TYPE,
        PERFORMANCE_SCORE
    FROM My_table
    WHERE
        TYPE = 'EX' 
)
a
INNER JOIN
(
    SELECT
        ID,
        NAME,
        DATE,
        TYPE,
        PERFORMANCE_SCORE
    FROM My_table
    WHERE
        TYPE = 'EN'
)
b
ON
    a.ID = b.ID AND a.NAME = b.NAME and b.PERFORMANCE_SCORE - a.PERFORMANCE_SCORE < 0

I get the records I need, but they are all in one row like below

ID     NAME       DATE       TYPE   PERFORMANCE_SCORE  ID     NAME       DATE       TYPE   PERFORMANCE_SCORE
105    Dave       06/22/18    EX      12               105    Dave       06/14/18    EN      16   
108    Ben        02/07/19    EX      15               108    Ben        02/01/19    EN      19

What am I missing?

Answer 1

With EXISTS:

select t.* from my_table t
where exists (
  select 1 from my_table
  where
  name = t.name
  and 
  type = case when t.type = 'EX' then 'EN' else 'EX' end
  and
  (performance_score - t.performance_score) * (case when t.type = 'EX' then 1 else -1 end) > 0
)