Postgres 11

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table test (
  id integer,
  pid integer,
  name varchar(2),
  val integer
);

create index ix_test__pid on test(pid);
create index ix_test__name_val on test(name,val);

insert into test
  (id, pid, name, val)
values
  ('1', '1', 'aa', '10'),
  ('2', '1', 'bb', '20'),
  ('3', '1', 'cc', '30'),
  ('4', '2', 'aa', '10'),
  ('5', '2', 'bb', '20'),
  ('6', '2', 'cc', '30'),
  ('7', '3', 'aa', '10'),
  ('8', '3', 'bb', '20'),
  ('9', '3', 'cc', '999');

CREATE TABLE

CREATE INDEX

CREATE INDEX

INSERT 0 9

select distinct pid from test 
where
pid in (select pid from test where (name,val) = ('aa',10))     
and pid in (select pid from test where (name,val) = ('bb',20))
and pid in (select pid from test where (name,val) = ('cc',30));

pid

2

1

SELECT 2

-- works on all RDBMS
select pid from test where (name,val) = ('aa',10)
and pid in (
    select pid from test where (name,val) = ('bb',20)
    and pid in (
        select pid from test where (name,val) = ('cc',30)
    )
);

pid

2

1

SELECT 2

-- works on most RDBMS, MySQL has no INTERSECT
select pid from test where (name,val) = ('aa',10)
intersect
select pid from test where (name,val) = ('bb',20)
intersect
select pid from test where (name,val) = ('cc',30);

pid

1

2

SELECT 2

-- works on all RDBMS
select a.pid
from   test a, test b, test c
where  (a.name,a.val) = ('aa',10) 
and    (b.name,b.val) = ('bb',20) 
and    (c.name,c.val) = ('cc',30) 
and    (a.pid = b.pid and b.pid = c.pid);

pid

2

1

SELECT 2

-- same as above. for JOIN purists
select a.pid
from   test a 
cross join test b
cross join test c
where  (a.name,a.val) = ('aa',10) 
and    (b.name,b.val) = ('bb',20) 
and    (c.name,c.val) = ('cc',30) 
and    (a.pid = b.pid and b.pid = c.pid);

pid

2

1

SELECT 2

-- just count  
select t.pid
from test t
where (t.name, t.val) in ( ('aa', '10'), ('bb', '20'), ('cc', '30') )
group by t.pid
having count(*) = 3;                                                     
                                                     
pid

1

2

SELECT 2

select t.pid
from test t
group by t.pid
having sum( ((t.name, t.val) in ( ('aa', '10'), ('bb', '20'), ('cc', '30') ))::int ) = 3

pid

2

1

SELECT 2