Re: Oracle Left Outer Join problem - first occurance of a match

On Oct 28, 12:24 pm, trp..._at_gmail.com wrote:
> I am trying to left outer join to a table with mismatched results...
> let me explain
>
> Table: first_names
> id     first
> 1      Tom
> 2      Mike
> 3      Jack
>
> Table: last_names
> id      last
> 1      Jones
> 2      Smith
> 2      Smiths
>
> When I query the tables here is what I get
>
> SELECT a.id, a.first, b.last FROM first_names a, last_names b
> WHERE a.id=b.id(+);
>
> Here is what I get
>
> id      first      last
> 1      Tom     Jones
> 2      Mike    Smith
> 2      Mike    Smiths
> 3      Jack
>
> What I really want to get is 3 results:
> id      first      last
> 1      Tom     Jones
> 2      Mike    Smith
> 3      Jack
>
> I just want to join to the first occurance of the id in the second
> table, no matter what the result is.
>
> I have simplified this query a good deal to show the problem I am
> having, so the data set should not be taken literally as I already see
> there will be questions as to why there would be duplicate id's with
> different last names, but without going into great detail on the
> actual query this is necessary for my circumstances.
>
> So is there a way to do a join and only pick up the first occurance of
> a match on the second table?
>
> Thanks!!

Possibly this will work:

SQL> create table first_names(

  2          id      number,
  3          first   varchar2(20)

Table created.

SQL>
SQL> create table last_names(

  2          id      number,
  3          last    varchar2(30)

Table created.

SQL>
SQL> insert all
  2 into first_names
  3 values (1, 'Tom')
  4 into first_names
  5 values (2, 'Mike')
  6 into first_names
  7 values (3, 'Jack')
  8 into last_names
  9 values (1, 'Jones')
 10 into last_names
 11 values (2, 'Smith')
 12 into last_names
 13 values (2, 'Smiths')
 14 select * From dual;

6 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select f.id, f.first, l.last
  2 from
  3 first_names f left outer join
  4 (select id, last

  5          from
  6          (select id, last, dense_rank() over (order by id, last)
rk
  7                  from last_names)
  8          where rk = id) l

        ID FIRST                LAST
---------- -------------------- ------------------------------
         1 Tom                  Jones
         2 Mike                 Smith
         3 Jack

SQL> David Fitzjarrell Received on Tue Oct 28 2008 - 15:43:24 CDT