Join one-to-many: Join parent record with *FIRST* matching child in SQL Select?

I have two tables, in a one-to-many relationship.

The first (one) table contains Project information. The second (many) contains 1 or more billing addresses for the Project. The tables are related on PROJ_ID.

I want to use a SQL select to join the two tables in to a single result set, so that each record in the result set contains columns from the Project table record along with columns from the *FIRST* matching billing address record.

The billing address table has a unique numeric key (ADDR_NO) which is going to indicate the order that the billing_address record is added to the table (this key will be generated using an Oracle sequence). However, this is not necessarily going to be 1 for billing address 1, 2 for billing address 2, etc. The only guarantee is that within a PROJ_ID, the ADDR_NO will be in correct sequence based on the order the billing address records for that PROJ_ID were added. However, you may get gaps (.i. for a particular project with 4 billing address records, the ADDR_NO columns may be 10, 15, 35, 40) if the user has gone off an added additional billing_address records for other projects and then come back, etc.

My question is: how do I phrase the SQL select to what I want.

Do I use an inner join between the two tables, and use an ORDER BY BILLING_ADDRESS.ADDR_NO GROUP BY PROJ_ID along with a COUNT(*) or something to force the join between the project record and lowest numbered billing_address?

Do I have to resort to using an explicit sequence column within the billing_address table that is always 1 for the first billing address, 2 for the second, and so on? I don't really want to resort to this If I can help it.

I'm prototyping using MS-Access, but the production back-end is Oracle 7.3.

Thanks. Received on Tue Jul 28 1998 - 00:58:07 CEST