select distinct c.id, t.set_name from rtneo_entityes_temporary_set t

join (select rets1.entity_id as id, count(rets1.set_name) as count from

(select distinct c.id as id from rtneo_contragent c join rtneo_entityes_temporary_set rets on c.id = rets.entity_id where rets.set_name = 'contragentFromFile' and c.delete_ts is null and rets.delete_ts is null

INTERSECT

select distinct c.id as id from rtneo_contragent c join rtneo_entityes_temporary_set rets on c.id = rets.entity_id where not rets.set_name = 'contragentFromFile' and c.delete_ts is null and rets.delete_ts is null) ct

join rtneo_entityes_temporary_set rets1 on ct.id = rets1.entity_id and not rets1.set_name = 'contragentFromFile'

group by rets1.entity_id having count(rets1.set_name) > 1) cg on t.entity_id = cg.id

join rtneo_contragent c on c.id = cg.id

where

t.delete_ts is null

and c.delete_ts is null

and not t.set_name = 'contragentFromFile'

and not t.set_name like 'CreatedContract%%'

-- and t.set_name = 'oneIsLogin04-10-20CreatedContract'

order by c.id