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.

What SQL have you tried so far? At the moment this reads like an exam question. — paulbailey
– paulbailey, Commented Dec 19, 2011 at 16:11
Before posting I'd tried the brute force method however the number of combinations required would make it difficult to adapt particularly if the combinations got bigger and the number of entries got bigger. I'd tried breaking it down into 5 separate SQL sub-queries but when it came to joining them it seemed to wind back to a brute-force style method. — revelo
– revelo, Commented Dec 19, 2011 at 21:01

user359040 · Accepted Answer · 2011-12-19 16:16:36Z

6

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

answered Dec 19, 2011 at 16:16

user359040

Sign up to request clarification or add additional context in comments.

Comments

Eugen Rieck · Accepted Answer · 2011-12-19 16:21:38Z

0

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.

answered Dec 19, 2011 at 16:21

Eugen Rieck

65.6k10 gold badges75 silver badges97 bronze badges

1 Comment

mwigdahl Over a year ago

Won't downvote because this would technically work, but this is far more convoluted and verbose than Mark's answer.

Collectives™ on Stack Overflow

SQL query: Finding unique number combinations within all records

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related