I have a specific problem and i didn't find any ressources to help me. Maybe i looked badly or maybe it's not possible to do what i want. I just changed the name of my columns but the spirit stays the same.

Is it possible to find the ID_postman in common with a list of ID_House but it most be strict ? If example, if i want the ID_postman in common with [H1,H2,H3], i must have :

But if I want the ID_postman in common with [H1,H2,H4], i must not have results.

where ID_house in ('H1','H2','H4') => result : none where ID_house in ('H1','H2','H3') => result : P1

CodePudding user response：

Seems tricker than it first appears but try the following as a possible solution, it should work in most compliant RDBMS platforms standard analytic functions.

with x as (
    select id_house, id_postman, 
      Count(*) over(partition by id_postman) pq
    from t
    where id_house in ('h1','h2','h3')
    group by id_house,id_postman
)
select id_postman
from x
where pq=(select Count(distinct id_house) from x)
group by id_postman 

See working Fiddle example

CodePudding user response：

I had this kind of problem. To solve it I found a unique way to represent the value targeted as concatenation of string ordered.

With windows function (or just GROUP BYs) you could link each postman with his ids_house

Something like

The tricks is to be sure to have no ambiguity in your id_house gathering, use an order by.

More elegant ways should exist to fix it but it worked for me.

CodePudding user response：

Probably you're looking for a request like this:

SELECT ID_Postman FROM my_table
WHERE ID_House IN ('H1', 'H2', 'H4);