Problem w/ Join

Hi. I'm having a problem joining two tables. Actually, the join is working, it's just not doing what I want. I want to join two tables, and find the product of two values (one from each table). There will be a match for each of table 1's rows in table 2. However, there will occasionally be multiple rows in table 2 to match a single row in table 1. Therefore, if I add up all my products my results will be inflated. I want to join the tables but only accept the first matching row in table 2 - does that make sense? Is it possible. Here is a graphical representation of what I'm trying to do:

Table_1

order     line     units
0           2        6
1           1        3
1           2        4
2           1        6
3           2        3


Table_2
order     line     cost
1           1        10
1           2        5
2           1        3
2           1        3
3           2        8

select a.order, a.line, a.units, b.cost, (a.units * b.cost)

   from table_1 a,

          table_2 b
 where a.order = b.order

     and a.line = b.line

My sql returns the following.

order     line     units  cost  (a.units * b.cost)
1           1        3       10    30
1           2        4       5      20
2           1        6       3      18
2           1        6       3      18    < I only want one row created for
order #2
3           2        3       8      24

Unfortunately, there is one line that I don't want returned. The fact that there are multiple rows in table 2 that match a single row in table 1 inflates my results. Can I modify the above query, somehow, to only match on one of the rows in table 2 so that my results would look like this:

order     line     units  cost  (a.units * b.cost)
1           1        3       10    30
1           2        4       5      20
2           1        6       3      18
3           2        3       8      24

Any help is really appreciated, I'm in a real jam here trying to make this thing work. Thank you all in advance,

C. Duncan Hudson Received on Wed Aug 11 1999 - 00:16:44 CDT