Q:

inner join on table with multiple record

I have two tables as follows
table1
column1|column2
----------------
a1     | c1
----------------
a2     | c2

table2
column1|column2|column3
----------------------
1      | a1    | x
-----------------------
2      | a1    | y
-----------------------
3      | a1    | z
-----------------------
4      | a2    | ab
-----------------------
5      | a2    | cd
-----------------------
6      | a2    | ef

Table1 is the parent table and table2 is the child table.
Column1 of table1 is mapped to column2 of table2.
Table2 can contain multiple records for each entry of column1 in table1 some thing like this.
I want to join table1 to table2, but I want only the first record of all entries of table2.
So my result set will be 
column1|column2|column3
----------------------
1      | a1    | x
-----------------------
2      | a2    | ab

How to make a join to achieve this in oracle 11 g. I use sql navigator +

A:

select
  x.column1,
  x.column2,
  x.column3
from
    (select
      t2.column1,
      t2.column2,
      t2.column3,
      dense_rank() over (partition by t2.column1, t2.column2 order by t2.column3) as rank
    from
      table1 t1
      inner join table2 t2 on t2.column2 = t1.column1) x
where
  x.rank = 1

But since you're not using any fields from t1, you may leave out the join. It could be used for filtering (which could just as well be done by using exists, but since t1 is the parent table, all records in t2 should have a matching t1 record anyway.

