MySQL SELECT where IN (associative array[key]) AND SUM(Col5) IN >= (array[key2])

Question

Let's say I have a table Colors with columns MainColor (Green, blue), Tone(44,17), Brand(Mybrand, Yourbrand, etc), Type(Standard, Special) and Availability What I need is to display, according to a request like:

Looking for 3x Green Tone 44, 8x Green Tone 17 and 5x Blue Tone 44

That stores in an array like this one:

Array
(
    [0] => Array
        (
            [MainColor] => Green
            [Tone] => 44
            [Needed] => 3
        )
    [1] => Array
        (
            [MainColor] => Blue
            [Tone] => 44
            [Needed] => 5
        )
    [2] => Array
        (
            [MainColor] => Green
            [Tone] => 17
            [Needed] => 8
        )

)

Something like

There are 3x Green Tone 44 (1x MyBrand Normal 1x Mybrand Special and 1x Yourbrand Standard)

and 8x Green Tone 17 (1x Herbrand Special 2x Herbrand Standard, 4x Ourbrand Standard and 1x Hisbrand Special)

and 5x Blue Tone 44 (3Mybrand Special and 2x Yourbrand Special)

available color(s)

I know I can achieve the first 2 statements with something like:

SELECT *
    FROM Colors  
    WHERE MainColor IN (array['MainColor']) AND Tone IN (array['Tone'])

And what about the third one that needs to be summed and compared (greater than or equals the need amount)?

SELECT *
    FROM Colors  
    WHERE MainColor IN (array['MainColor']) AND Tone IN (array['Tone']) AND SUM(Colors.Availability) IN >= (array['Needed'])

Answer 1

I think that you need to find all brands that have that particular colour/tone, so ignore the amounts, just list them if there are any available.

Then let the user select the quantity they want from each brand in a web page. At this point you could enforce that the sum of the brands they select is as they have already entered (could do some JS to do this prior to submit) or could simply let them have as many or little as they want.

Only difficulty is if two people are offered the same colours/tones and they both try and order more stock than you have. You will then have to check when they finally order them that the stock is still there.