sql - postgreSQL Combinations -

sql - postgreSQL Combinations -

i'm trying figure out how find possible combinations (using sql) next situation:

i have 100 ping pong balls in bowl (id = 1...100) each ball 1 of 4 colors (color = red, green, blue, yellow)

i want pick 5 balls (without replacement) follows.

1 reddish ball 2 greenish balls 3 bluish balls 2 yellowish balls 1 ball green, blue, or yellow

how can determine possible combinations using sql efficiently possible?

below best come with, don't want order matter (combinations) , want no replacement:

select pick1.id, pick2.id, pick3.id, pick4.id, pick5.id, pick6.id, pick7.id, pick8.id, pick9.id bowl pick1, bowl pick2, bowl pick3, bowl pick4, bowl pick5, bowl pick6, bowl pick7, bowl pick8, bowl pick9 pick1.color = "red" , pick2.color = "green" , pick3.color = "green" , pick4.color = "blue" , pick5.color = "blue" , pick6.color = "blue" , pick7.color = "yellow" , pick8.color = "yellow" , (pick9.color = "green" or pick9.color = "blue" or pick9.color = "yellow")

i haven't tried in actual postgresql server here idea.

first codify colors in integers:

0 = reddish 1 = greenish 2 = bluish 3 = yellow

now, example, want draw 3 balls: 1 red, 1 green, , 1 greenish or yellow. corresponding color codes, after sorting, used filter in clause of final sql statement:

[0, 1, 1] [0, 1, 3]

then not in (...) ensures there no repeating ids, , sorted array of colors limited set specified above.

create extension intarray; select p1.id, p2.id, p3.id bowl p1 cross bring together bowl p2 cross bring together bowl p3 p2.id not in (p1.id) , p3.id not in (p1.id, p2.id) , sort(int[p1.color, p2.color, p3.color]) in ( int[0,1,1], int[0,1,3] )

the intarray extension needed sort() function.

a variation not involving array[] nor intarray extension possible long list out desired combinations of colors in in (..) predicate. see link.

sql postgresql combinations permutation