Oracle Left Outer Join problem - first occurance of a match

From: <trpost_at_gmail.com>
Date: Tue, 28 Oct 2008 10:24:58 -0700 (PDT)
Message-ID: <9290db80-0c54-46e3-b295-c99f21dc769a@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!! Received on Tue Oct 28 2008 - 12:24:58 CDT

Original text of this message