clear markdown compare help donate comments/suggestions/bugs a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith?
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 363571 distinct fiddles created so far.

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');
9 rows affected
 hidden batch(es)


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
 hidden batch(es)


-- 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
 hidden batch(es)


-- 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
 hidden batch(es)


-- 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
 hidden batch(es)


-- 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
 hidden batch(es)


-- 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
 hidden batch(es)


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
 hidden batch(es)