Question: Please assist me for complete this query 

```
select *
from table1 a
join table2_n n 
on a.PHONE_NO like '%'|| (select substr(mobile_no,2,9) as mobile_no from table2)

```

table 2 records exists in table1. so i need to get them all from this query

Answer: 
You don't need to have a subquery, you can just join the data directly:

```
select a.*
from   table1 a
join   table2 n 
on     a.PHONE_NO like '%'|| substr(mobile_no,2,9);

```

Alternatively, you could write this using an `exists` subquery:

```
select *
from   table1 a
where  exists (select null
               from   table2 n 
               where  a.PHONE_NO like '%'|| substr(mobile_no,2,9));

```

If you have multiple records in table2 matching those in table1, the first query will return you multiple rows from t1 unless you put a `distinct` clause in there.
Use rownum=1 or where condition to return only one row from the inner sub query otherwise it would not work for multiple recods in table2.