Tough SQL Query

[Ronald Lopert]

Ok I have the following data stored in a table and I need to query it, the problem is I need to get multiple combinations for all 3 fields. Not sure if I can do this with sql or whether I need to code it programmatically, hope someone can help. My data is as follows


id bid price1 price2 price3
-- --- ------ ------ ------
1 1 1.20 3.00 2.20
2 1 1.15 3.10 0.00
3 1 1.18 3.20 2.30

bid represents an id that groups these prices together so for bid 1 I need the following results.

price1 price2 price3
------ ------ ------
1.20 3.00 2.20
1.20 3.00 2.30
1.20 3.10 2.20
1.20 3.10 2.30
1.20 3.20 2.20
1.20 3.20 2.30
1.15 3.00 2.20
1.15 3.00 0.00
1.15 3.00 2.30
1.15 3.10 2.20
1.15 3.10 0.00
1.15 3.10 2.30
1.15 3.20 2.20
1.15 3.20 0.00
1.15 3.20 2.30
1.18 3.00 2.20
1.18 3.00 2.30
1.18 3.10 2.20
1.18 3.10 2.30
1.18 3.20 2.20
1.18 3.20 2.30

The rules for the results we see here are as follows

1. If a row has a price3 > 0 get the other combos of price2 and price3 to go with price1
2. If a row has a price3 = 0 get the other combos of price2 and price3 but also get the combos on price1 and price2 assuming price3 is 0

I was trying to use a self join 3 times somthing like

select a.price1, b.price2, c.price3 from test a, test b, test c where a.bid = b.bid and a.bid = c.bid

but I can't figure out a way to exclude the 0 I don't want and to include the extra 0's I need. Can someone advise if this is even possible to do with an sql query.