Re: Problem w/ Join

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

   from table_1 a,

          (select distinct order,line, cost from table_2) b  where a.order = b.order

     and a.line = b.line

HTH, Philippe

the.hudsons_at_abac.com wrote in message <7or13d$48s$1_at_news1.abac.com>...
>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 - 05:42:51 CDT