CODEWITHSUNDEEP
sqlpostgresql
pondia
pondia

Reputation: 43

Remove all rows corresponding a specific ID if the value of a column match

I am trying to delete all lines containing an ID if the value of a column in one of the rows matches the value sought

I have the following tables :

table1: 

|   ID   |  |  offer    |
-------------------------
|   001  |  |  'SE;SL'  |
|   001  |  |   'WX'    |
|   001  |  |   'PW'    |

|   002  |  |   'WS'    |
|   002  |  |   'EL'    |

|   003  |  |   'SE'    |

|   004  |  |   'ZS'    |
|   004  |  |'PW;SE;ODL'|

SELECT  ID,
        offer
FROM table1
GROUP BY 
         ID,
         offer,
HAVING sum(case when offer LIKE '%SE%' then 1 else 0 end) = 0

For the moment, I am able to delete the line as follows:

|   ID   |  |  offer    |
-------------------------
|   001  |  |   'WX'    |
|   001  |  |   'PW'    |

|   002  |  |   'WS'    |
|   002  |  |   'EL'    |

|   004  |  |   'ZS'    |

Expected result

|   ID   |  |  offer    |
-------------------------
|   002  |  |   'WS'    |
|   002  |  |   'EL'    |

As you can see, I try to isolate all the IDs for which it appears at least once "SE" in one of the rows of the "offer" column.

I have tried many things, done several searches without success, if someone can help me understand!

Thanks so muchh

Upvotes: 1

Views: 1545

Answers (2)

forpas
forpas

Reputation: 164164

With NOT EXISTS but you must check also the case the value you search is not part of another value.
So concatenate both the offer column and the value with ; at the start and at the end:

select id, offer
from table1 t
where not exists ( 
  select 1 from  table1 
  where 
    id=t.id
    and 
    ';' || offer || ';' like '%;SE;%'
)

Edit
Replace the last line with:

chr(59) || offer || chr(59) like '%' || chr(59) || 'SE' || chr(59) || '%'

Upvotes: 1

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31991

use not exists

SELECT  ID,
        offer
FROM table1 t1
where not exists ( select 1 from  table1 t2 where t1.id=t2.id
                        and t2.offer like '%SE%')

Upvotes: 1

Related Questions