SQL query: Finding unique number combinations within all records

Question

I am trying to create a query in SQL Server that will search for all combinations of numbers in a table.

COMBINATION TABLE

CombID     Comb_Num1     Comb_NumTwo     Comb_NumThree
   1           1              2                3
   2           2              10               15
   3           5              20               60
   4           10             22               50
   5           22             33               46           

The numbers range from 1-60, and the same number is not repeated within a combination. Order does not matter.

ENTRY TABLE

EntryID     NumberOne     NumberTwo     NumberThree     NumberFour     NumberFive
   1            10           22             33              46              50
   2            2            10             15              22              40
   3            24           33             40              45              50
   4            5            10             22              40              60
   5            2            6              10              22              40
   6            2            10             22              50              60
   7            10           22             33              46              50

The numbers range from 1-60, and the same number is not repeated within an entry. Order does not matter.

Results

• Searching for combination 1 would produce no results
• Searching for combination 2 would return EntryID 2
• Searching for combination 3 would produce no results
• Searching for combination 4 would return EntryID 1, 6, 7
• Searching for combination 5 would return EntryID 1, 7

The query should also show for each record in the Combination table how many times it has appeared in the Entry table. It should exclude the combinations that don't appear in the Entry table.

Answer 1

Brute force would do this:

SELECT EntryID
FROM combinations
INNER JOIN entries ON
  (Comb_Num1=NumberOne AND Comb_NumTwo=NumberTwo AND Comb_NumThree=NumberThree)
  OR (Comb_Num1=NumberTwo AND Comb_NumTwo=NumberThree AND Comb_NumThree=NumberFour)
  OR (Comb_Num1=NumberThree AND Comb_NumTwo=NumberFour AND Comb_NumThree=NumberFive)
WHERE CombID=<whatever>

this ofourse DOES take into account the order, which you don't want. To fix this either you create another table (one time creation) that has the same CombID for all permutations of Comb_Num1, CombNumTwo and CombNumThree) or you extend the insane join condition in the same way. This is left to the reader as an exercise.

Answer 2

Try:

select distinct e.EntryID
from entry e, combination c
where c.Comb_Num1 in (e.NumberOne, e.NumberTwo, e.NumberThree, e.NumberFour, e.NumberFive)
and   c.Comb_Num2 in (e.NumberOne, e.NumberTwo, e.NumberThree, e.NumberFour, e.NumberFive)
and   c.Comb_Num3 in (e.NumberOne, e.NumberTwo, e.NumberThree, e.NumberFour, e.NumberFive)
and   c.CombID = @CombID

- to return matching entries for a specific @CombID