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

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