PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Find all those rows which has a matching exclude rows in same table

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

Update 13/06/2024:

It looks like the requirement has changed to find all those included rows minus the excluded rows. This means if a sp_id has both EA/B & IA/B then that row should not be in the resultset. Also, if EA/B has null value in sp_id then nothing is excluded.

This is what I tried to find out the excluded ones which should be removed from final output. I am sure this can be extended to exclude these from final output.

WITH    --  S a m p l e    D a t a
  tbl ( id, a_id, b_type, e_id, sp_id, br_type ) AS
    ( Select 65593, '65593_C',  'EA',   238,    null,   'B' Union All
      Select 65593, '65593_C',  'EB',   155,    '72',     'B' Union All
      Select 65593, '65593_C',  'IB',   155,    '72',     'B' Union All
      Select 65593, '65593_C',  'IA',   238,    '1',      'B' Union All
      Select 65593, '65593_C',  'EA',   238,    '1',      'B' Union All
      Select 65593, '65593_C',  'IA',   238,    '2',      'B' Union All
      Select 65593, '65593_C',  'IA',   238,    '23',     'B' Union All
      Select 65593, '65593_C',  'IA',   238,    '3',      'H'
    )
select distinct b1.ID
  ,b1.A_ID
  ,b1.B_TYPE
  ,b1.E_ID
  ,b1.SP_ID
  ,b1.BR_TYPE
from tbl b1
inner join tbl b2 on b1.ID = b2.id
                and b1.A_ID = b2.A_ID 
                and b1.E_ID = b2.E_ID 
                and b1.SP_ID = b2.SP_ID
where b1.B_TYPE in ('EA', 'EB')--, 'IA', 'IB')
  and (b1.SP_ID is not null )--and b1.SP_ID != '')

Many thanks in advance for your help.
AK

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    The following query did the trick for me.

    WITH    --  S a m p l e    D a t a
  tbl ( id, a_id, b_type, e_id, sp_id, br_type ) AS
    ( Select 65593, '65593_C',  'EA',   238,    null,   'B' Union All
      Select 65593, '65593_C',  'EB',   155,    '72',     'B' Union All
      Select 65593, '65593_C',  'IB',   155,    '72',     'B' Union All
      Select 65593, '65593_C',  'IA',   238,    '1',      'B' Union All
      Select 65593, '65593_C',  'EA',   238,    '1',      'B' Union All
      Select 65593, '65593_C',  'IA',   238,    '2',      'B' Union All
      Select 65593, '65593_C',  'IA',   238,    '23',     'B' Union All
      Select 65593, '65593_C',  'IA',   238,    '3',      'H'
    )
select distinct b1.ID
  ,b1.A_ID
  ,b1.B_TYPE
  ,b1.E_ID
  ,b1.SP_ID
  ,b1.BR_TYPE
from tbl b1
where (sp_id is not null or sp_id != '')
  and not exists (select 1 from tbl 
                  where b1.ID = id
                    and b1.A_ID = A_ID 
                    and b1.E_ID = E_ID 
                    and b1.SP_ID = SP_ID
                    and b1.B_TYPE in ('EA', 'EB'));

  2. 0

    Does this help?

    WITH    --  S a m p l e    D a t a
  tbl ( id, a_id, b_type, e_id, sp_id, br_type ) AS
    ( Select 65593, '65593_C',  'EA',   238,    null,   'B' Union All
      Select 65593, '65593_C',  'EB',   155,    72,     'B' Union All
      Select 65593, '65593_C',  'IB',   155,    72,     'B' Union All
      Select 65593, '65593_C',  'IA',   238,    1,      'B' Union All
      Select 65593, '65593_C',  'EA',   238,    1,      'B' Union All
      Select 65593, '65593_C',  'IA',   238,    2,      'B' Union All
      Select 65593, '65593_C',  'IA',   238,    23,     'B' Union All
      Select 65593, '65593_C',  'IA',   238,    3,      'H'
    )

    --    S Q L :
Select    t.*
From      tbl t
Where     t.sp_id Is Not Null And 
          SubStr(t.b_type, 1, 1) = 'E'

    /*      R e s u l t :
   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        */
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search