better way to implement the following operations in MySQL?

Question

I get a list of options with price like the following: (it's the result from a select query sort by price asc)

    price   color   quanlity
o_id            
1     2     R       medium 
3     3     G       bad
4     4     G       good
5     6     B       good
2     8     R       medium

Now I need to pair those options according to requirements:

e.g. if I need 2 R(red) and 4 G(green)

I'd like to return a list of possible combinations (sort by price asc), like:

        R(2)    G(4)    
c_id    o_id    o_id    total price
1          1    3       16
2          1    4       20
3          2    3       28
4          2    4       32

My current solution for this is to make multiple queries to the DB:

(I'm using Java at the application layer / back end.)

1. select distinct colors, and store it in a List
2. In a For loop, select options of each color into a different temp table
3. join the List of Tables, and calculate the total, sort by total.

But is there a way to condense the above operations into a stored procedure or something more elegant?

Answer 1

You just need a simple self-join:

SELECT   R.o_id AS R_id, G.o_id AS G_id, 2*R.price + 4*G.price AS total
FROM     mytable R JOIN mytable G ON R.color = 'R' AND G.color = 'G'
ORDER BY total

See it on sqlfiddle.