select record_id
  from lookup
 where name = 'JOHN'

##</code><code>##

NAME   RECORD_ID
----   ---------
JOHN    1
SMITH   1
BILL    2
SMITH   2
JOHN    3
DEER    3
JOHN    4
DOE     4

##</code><code>##

select record_id
  from lookup
 where name in ('JOHN', 'SMITH')
 group by record_id