All geek questions in one place

SQL query: search for unique combinations of numbers in all records

I am trying to create a query in SQL Server that will look 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

Numbers range from 1-60, and the same number is not repeated in combination. The order does not matter.

ENTRANCE TABLES

 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

Numbers range from 1-60, and the same number is not repeated in the record. The order does not matter.

results

  • Searching for combination 1 will not produce results
  • Search for combination 2 returns EntryID 2
  • Searching for combination 3 will not produce results
  • Searching for combination 4 will return EntryID 1, 6, 7
  • Searching for combination 5 returns EntryID 1, 7

The query should also show each entry in the combination table, how many times it has appeared in the Entry table. It should exclude combinations that are not displayed in the input table.

+4
source share
2 answers

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

- return the corresponding entries for a specific @CombID

+6
source

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, of course, takes into account the order you do not want. To fix this, you create another table (one creation time) that has the same CombID for all permutations Comb_Num1, CombNumTwo and CombNumThree), or you extend the condition of the crazy join. This remains for the reader as an exercise.

0
source

Source: https://habr.com/ru/post/1387059/

More articles:


All Articles