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

<trpost_at_gmail.com> wrote in message
news:9290db80-0c54-46e3-b295-c99f21dc769a_at_v13g2000pro.googlegroups.com...
>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!!
>

By first occurrence, do you mean the row with the smallest rowid? As you may know, the use of rowid in queries is not a good practice. The result of this query will vary depending on which order rows are stored. Sorry it just makes no application sense to me. Received on Wed Oct 29 2008 - 20:37:13 CDT