I have a table like below. For a given id & a_id there could be different combinations of b_type, e_id, sp_id. br_type can be either B or H. b_type values EA means Excluded Party A, EB means Excluded Party B, IA means Included Party A and IB means Included Party B.
| id | a_id | b_type | e_id | sp_id | br_type |
|---|---|---|---|---|---|
| 65593 | 65593_C | EA | 238 | null | B |
| 65593 | 65593_C | EB | 155 | 72 | B |
| 65593 | 65593_C | IA | 238 | 1 | B |
| 65593 | 65593_C | EA | 238 | 1 | B |
| 65593 | 65593_C | IA | 238 | 2 | B |
| 65593 | 65593_C | IA | 238 | 23 | B |
| 65593 | 65593_C | IA | 238 | 3 | H |
If sp_id is null for a given id, a_id & e_id then nothing is excluded for that e_id. However, if sp_id has a value with b_type as EA then that sp_id is excluded. Here, sp_id = 1 has both EA & IA so the final result will be EA.
I would like a query which gives me those rows which are excluded based on included values such that the output will be like this.
| id | a_id | b_type | e_id | sp_id | br_type |
|---|---|---|---|---|---|
| 65593 | 65593_C | EB | 155 | 72 | B |
| 65593 | 65593_C | EA | 238 | 1 | B |
Many thanks in advance for your help.
AK