I have a table of following structure

Parent ID number, Child ID number, Address varchar2(100)

I want to write a SQL that pulls all records by searching on Address field.

Only twist is, i would like all records of the {parent id} combination if even one of the {child} had a {address} criteria match.

I know we would code this in say this way,

```
SELECT parent_id, child_id, address
  FROM mytable t
 WHERE EXISTS
          (SELECT 1
             FROM mytable t2
            WHERE t.parent_id = t2.parent_id
                  AND address LIKE :p_search_criteria);

```

But is there any better solution than this? Key is that we are searching within the same table that we are retrieving from... 

This is Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit