Re: sql return only first matching record from join table

There is no such thing as "First" in relational database theory. The data in tables is stored as a set. A set, by definition, is unordered. That being said, all you need to do is:

select table1.address, table2.name
 from table1, table2
where address = '123 Main St'
and table1.keyid = table2.keyid
AND rownum < 2
;

You will always get one row (assuming there is a row), which sounds like it will solve your problem.. No such thing as first.

"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 Wed Jul 03 2002 - 09:08:47 CDT