Re: SQL query problem

Hi Bob,

I can think of a couple of ways you could do this. If you don't need the actual
amounts and just want to order by the combined cost then you could just do a
group by and order by the sum of the amount.

select
a.field1,a.field2
from table1 a ,table2 b
where a.field1 = b.field1
or
a.field2 = b.field1
group by a.field1,a.field2
order by sum(b.field2)

If you
need to return the amount then you could use :

select
a.field1,a.field2,b.field2,c.field2
from table1 a ,table2 b, table2 c
where
a.field1 = b.field1
and a.field2 = c.field1
order by (b.field2+c.field2)
The problem with this is that if an amount is missing in table2 for either of the
fields in table1 then it will not be returned unless you use outer joins. I would
think about restructuring the data if possible avoiding the join to table2 on 1 of
the two possible columns.

Hope this helps,

Dean.

In
article <34f5e153.178726084_at_news.internetmci.com>,   bobmc_at_cyberramp.net (Bob
McConnell) wrote:
>
> I am trying to resolve the following query problem:
>
 

> table 1 contains 2 fields containing product discriptions such as:
>

 field 1 field 2
> apples oranges
> apples apples
> oranges bananas
>

 pears apples
> etc.
> the fields may or maynot contain the same
 description.
>
> table 2
> field1 field2
> apples 10
> pears 20
>

 bananas 30
> oranges 50
>
> The problem is to select field 1 and 2 from
 table 1 and order the
> results by values found in table 2
>
> I would like

 to do some sort of join where my select of all records
> from table 1 would

 return something liker:
> apples oranges 10 50
> apples apples 10 10
>

 oranges bananas 50 30
> pears apples 20 10
> allowing me to order the

 final results to be
> apples apples
> pears apples
> apples oranges
>

 oranges bananas
>
> Can someone point me in the right direction?
>
>

 Thank,
> Bob
>
>

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Sat Feb 28 1998 - 00:00:00 CST