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

On Oct 28, 6:52 pm, trp..._at_gmail.com wrote:
> On Oct 28, 2:43 pm, ddf <orat..._at_msn.com> wrote:
>
>
>
>
>
> > 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)
> >   4  );
>
> > Table created.
>
> > SQL>
> > SQL> create table last_names(
> >   2          id      number,
> >   3          last    varchar2(30)
> >   4  );
>
> > 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
> >   9  on l.id = f.id
> >  10  /
>
> >         ID FIRST                LAST
> > ---------- -------------------- ------------------------------
> >          1 Tom                  Jones
> >          2 Mike                 Smith
> >          3 Jack
>
> > SQL>
>
> > David Fitzjarrell- Hide quoted text -
>
> > - Show quoted text -
>
> Thanks! This is close, however the id's actually are non linear and
> look something like 1001922 so rank doesn't work so well
>
> So I guess to make my example better reflect what I am doing, the
> tables should look more like this
>
> SQL> insert all
>
> >   2  into first_names
> >   3  values (1001922, 'Tom')
> >   4  into first_names
> >   5  values (1001957, 'Mike')
> >   6  into first_names
> >   7  values (1001988, 'Jack')
> >   8  into last_names
> >   9  values (1001922, 'Jones')
> >  10  into last_names
> >  11  values (1001957, 'Smith')
> >  12  into last_names
> >  13  values (1001957, 'Smiths')
> >  14  select * From dual;
>
> Table: first_names
> id                first
> 1001922      Tom
> 1001957      Mike
> 1001988      Jack
>
> Table: last_names
> id                last
> 1001922      Jones
> 1001957      Smith
> 1001957      Smiths- Hide quoted text -
>
> - Show quoted text -

Using RANK() in place of DENSE_RANK() and using only the ID I get this with your 'new' data:

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, rank() over (order by id) rk
  7                  from last_names)
  8          where rk = rownum) l

        ID FIRST                LAST
---------- -------------------- ------------------------------
   1001922 Tom                  Jones
   1001957 Mike                 Smiths

SQL> David Fitzjarrell Received on Wed Oct 29 2008 - 10:47:09 CDT