A blog

Solving Sudoku in One SQL Query – Part 3 – Solving the Puzzle

July 19, 2016July 5, 2016

Okay, now that we have the general framework we can start writing the pieces that deal with the values that were given to us in the puzzle. The first thing I’m going to do is create a table that contains each possible value for each cell based on the values that are already in the solution.

Transact-SQL

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

Possibilities

AS(

SELECT[g].[Row]-- first select just sets the value for the given locations.

,[g].[Col]

,[locations].[loc]

,[g].[Data]

FROM@givenAS[g]

JOIN[locations]

ON[locations].[col]=[g].[Col]

AND[locations].[row]=[g].[Row]

UNIONALL

SELECT[possibility].[row]

,[possibility].[col]

,[possibility].[loc]

,[possibility].[val]

FROM(

SELECT[locations].*

,[possibleValue].[val]

FROM[locations]--

LEFTOUTERJOIN@givenAS[g]-- I join to the given values to exclude these below

ON[g].[Col]=[locations].[col]

AND[g].[Row]=[locations].[row]

CROSSAPPLY (

SELECT*FROM[Cnt]-- Join to all values 1-9

)AS[possibleValue]

WHERE[g].[Data]ISNULL--Exclude cells already defined in the given set

)AS[possibility]

WHERE[possibility].[val]NOTIN-- At this point, every cell not in the given set is

(

SELECT[existingCell].[Data]

FROM(

SELECT[locations].[loc]

,[g].[Data]

FROM@givenAS[g]

JOIN[locations]

ON[locations].[row]=[g].[Row]

AND[locations].[col]=[g].[Col]

)AS[existingCell]

JOIN[groups][existingCellGroup]

ON[existingCellGroup].[loc]=[existingCell].[loc]

JOIN[groups][possibleValueGroup]

ON[existingCellGroup].[grouping]=[possibleValueGroup].[grouping]

AND[existingCellGroup].[loc]!=[possibleValueGroup].[loc]

WHERE[possibleValueGroup].[loc]=[possibility].[loc]

)

),

First we get all the cells that are already given to us. These cells will only have one valid option. Next we need to union those with all the possible values for all the other cells. We could just include all values 1 thru 9 for each of these cells, but we already know that some values will be impossible because they already exist in a group that the cell belongs to. So to reduce our sample space, we do a check to see if the value already exists in any of the cells that are a member of a group that our cell is a member of.