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

On Oct 29, 7:37 pm, "Bob Jones" <em..._at_me.not> wrote:
> <trp..._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.- Hide quoted text -
>
> - Show quoted text -

Yeah I knew it would raise questions... I did not design the original database which I have some issues with, but am just trying to run a report from it... In the database there will be one login id, shared by many users, but differntiated by login/password primary key combination.. i.e.:

login_id      pass
user1         11111
user1         22222
user1         33333
user2         44444
user3         55555

so what I am doing is getting a total from a different join based on the login_id i.e.

login_id     count
user1        5000
user2        1050
user3        2200

Now the last piece is I wanted to tie a company name to the login_id as that is how logins are grouped. The problem is whoever designed the database assigned a company name to each login/password pair.

The company name is manually entered and I noticed that sometimes there is a small typo, which results in duplicate entries when I join the data. i.e.

login_id     count   company
user1        5000    Microsoft
user1        5000    Micro soft
user2        1050    Google
user3        2200    Yahoo

This is an internal report and I am not real picky if the result shows the typo or shows the correct name that is why I just want the first one, whatever it may be.

I know you could argue fix the data or fix the design, but I am just working with what I have for a very legacy system.

Thanks for all your assistance! Received on Thu Oct 30 2008 - 18:15:44 CDT