Oracle SQL to find entries matching two conditions in one table

Question

I have a table in an Oracle database which contains

Object	Extension_key	Extension_value
AAA	IpAddress	1.2.3.4
AAA	Userid	User1
AAA	DownloadDirectory	/homeA
BBB	IpAddress	1.2.3.4
BBB	Userid	User2
BBB	DownloadDirectory	/homeB
CCC	IpAddress	5.6.7.8
CCC	Userid	User3
CCC	DownloadDirectory	/homeC
DDD	IpAddress	1.2.3.4
DDD	Userid	User1
DDD	DownloadDirectory	/homeD

What is the query that would find all of the Object entries when IPAddress is 1.2.3.4 and Userid is User1 ?

Answer 1

You can use group by and having:

select object
from mytable
where (extension_key, extension_value) in ( ('IpAddress', '1.2.3.4'), ('Userid', 'User1') )
group by object
having count(*) = 2

The idea is to filter the table for the two key/value tuples that you are interested in; the we aggregate by object, and retain only objects that matched on both predicates.

Note that this assumes no duplicates in the table - otherwise we would need distinct in the having clause:

having count(distinct extension_key) = 2