Simplify Exist Query in Oracle

Question

How can I simplify the below query to return field2 only if there exist records with key_id ECRU and MTR.

SELECT
    z.field2
FROM
    mytable z
WHERE
        z.key_id = 'ECRU'
    AND EXISTS (
        SELECT
            1
        FROM
            mytable
        WHERE
                key_id = 'MTR'
            AND field2 = z.field2
    )

Answer 1

You could use group by and having:

select field2
from mytable
where key_id in ('ECRU', 'MTR')
group by field2
having count(*) = 2

This assumes no duplicate (field2, key_id). Otherwise, you need to slightly change the having clause:

having count(distinct key_id) = 2