Cartesian Join

Hello,
I have a table tblCustomer with fields CustomerName and Revenue. I have a
second table with Revenue ranges
LowRange HighRange Bucket
0 10 1
10 20 2
20 30 3
Is there a way to use a Cross Join to create the following output
Customer Revenue Bucket
Cust1 5 1
Cust2 28 3
--
Thanks in advance,
Steven"Steven K" <skaper@.troop.com> wrote in message
news:e5ySMdqvDHA.2148@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I have a table tblCustomer with fields CustomerName and Revenue. I have a
> second table with Revenue ranges
> LowRange HighRange Bucket
> 0 10 1
> 10 20 2
> 20 30 3
> Is there a way to use a Cross Join to create the following output
> Customer Revenue Bucket
> Cust1 5 1
> Cust2 28 3
>
> --
Sure. It's not even a Cross Join, since each customer is matched with one
Bucket.
Select Customer, Revenue, Bucket
from Customer join Bucket
on Customer.Revenue >= Bucket.LowRange
and Customer.Revenue < Bucket.HighRange
David|||It's not a cross join. Use a LEFT OUTER, RIGHT OUTER or INNER join depending
on your exact requirements:
SELECT C.customer, C.revenue, R.bucket
FROM Customers AS C
LEFT JOIN RevenueRanges AS R
ON C.revenue >= R.lowrange AND C.revenue < R.highrange
I'm assuming that none of the ranges in RevenueRanges will overlap.
--
David Portas
--
Please reply only to the newsgroup
--