Re: sql return only first matching record from join table

I posted this in a follow-up to Martin, but I think the answer to my second question is to place the (select min(rowid)...)as x in the from clause and alias that (x) and then use an outer join on x in the where clause something like ... and occupant.rowid(+) in x.rowid. This seems to work, but if anyone has any suggestions/improvements I'd appreciate hearing them.

Thanks

(Barnoit) wrote in message news:<cd2c60c8.0207031747.2eaf15f6_at_posting.google.com>...
> I've figured it out, but I've also created a new problem. I'm now
> using
> ... and occupant.rowid IN (SELECT MIN(occupant.rowid) FROM occupant
> GROUP BY occupant.key_id)
> which actually does what I want, however what I really need is this:
> ... and occupant.rowid(+) IN (SELECT MIN(occupant.rowid) FROM occupant
> GROUP BY key_id) *note the outer join*. This is not valid in oracle
> (can't have an outerjoin with a subquery). To make things more
> complicated from my original post, the query actually checks 2 tables
> for the occupant name, and if it doesn't exist in one I need to check
> the other, hence the outer join. The way the query is written, I
> actually return them both in the select statement, and I have some
> external logic to grab the appropriate value. Is there another way to
> do this outer join on a subquery?
>
> Thanks
>
>
>
> "Richard Foote" <richard.foote_at_bigpond.com> wrote in message news:<IBDU8.26883$Hj3.82905_at_newsfeeds.bigpond.com>...
> > Hi Barnoit,
> >
> > Simply add 'and rownum=1' to the where condition. Oracle will then only
> > return the first row that meets the other conditions.
> >
> > Good Luck
> >
> > Richard
> > "Barnoit" <jbarney_ca_at_yahoo.ca> wrote in message
> > news:cd2c60c8.0207030600.39d024b8_at_posting.google.com...
> > > Just to simplify my example, I have 2 tables that are joined on a key
> > > id (there are actually quite a few joins, but this is the one causing
> > > me trouble). The keyid on the first table is unique, but there is a
> > > one to many relationship in the second table (it is actually a street
> > > address table and an occupant table). What I need to be able to do is
> > > to a select using the keyid as the join criteria, but I only want to
> > > return the first matching record and disregard any other matches. So
> > > just to reconfirm, I want to do a select based on an address, join
> > > based on the keyid and return the name of the first person that it
> > > finds, disregarding any other occupants it finds. Any suggestions
> > > would be greatly appreciated.
> > > ex.
> > > select table1.address, table2.name
> > > from table1, table2
> > > where address = '123 Main St'
> > > and table1.keyid = table2.keyid
> > >
> > > What I am getting is
> > > 123 Main St, Joe Blow
> > > 123 Main St, John Q. Public ...
> > >
> > > but what I really want is whatever the first match is. I can't use
> > > pl/sql in this case, has to be sql only. I'm hoping I'm just
> > > overlooking something very simple, and BTW this is on oracle 8.0.5
Received on Thu Jul 04 2002 - 09:34:42 CDT