CODEWITHSUNDEEP
sqljoincountcompareprocedure
user1163512
user1163512

Reputation: 1

I need to compare results from two different tables to get the number of occurrence

I have a database with 2 tables: Table 1 is Entries (userID, number1, number2, number3, number4, number5, number6) Table 2 is Result (number1, number2, number3, number4, number5, number6)

The Entries table stores 6 numbers guessed for each userID and has about 1,200 rows already. The Result table stores 6 numbers and has only one row.

The problem now is:

I need to generate a report that will 1. compare the 6 numbers in the Result table against each and every row of the Entries table 2. count the numbers a user guessed correctly.

For example, if the Results were 1,2,3,4,5,6 and userA guessed 1,3,5,7,9,11, then the correct number of guesses for userA user is 3. If userB guessed 2,8,11,12,13,24, then the correct number of guesses for userB is 1. If userC guesses 23,24,25,26,27,28, then the correct number of guesses for userC is 0.

Therefore my report needs to contain 1,200 rows with 2 columns (userID and Correct_Number_of_Guesses).

What is the best approach towards this problem?

Upvotes: 0

Views: 143

Answers (2)

DavidEG
DavidEG

Reputation: 5957

SELECT userID
     , Case When t1.number1 = t2.number1 Then 1 Else 0 End +
          Case When t1.number2 = t2.number2 Then 1 Else 0 End +
          Case When t1.number3 = t2.number3 Then 1 Else 0 End +
          Case When t1.number4 = t2.number4 Then 1 Else 0 End +
          Case When t1.number5 = t2.number5 Then 1 Else 0 End +
          Case When t1.number6 = t2.number6 Then 1 Else 0 End as CorrectGuesses
  FROM Table1 t1
  cross join Table2 t2

Upvotes: 1

dani herrera
dani herrera

Reputation: 51715

With MySQL IF syntax:

SELECT 
     userID,
      if( t1.number1 = t2.number1, 1, 0 )+
      if( t1.number2 = t2.number2, 1, 0 )+
      if( t1.number3 = t2.number3, 1, 0 )+
      if( t1.number4 = t2.number4, 1, 0 )+
      if( t1.number5 = t2.number5, 1, 0 )+
      if( t1.number6 = t2.number6, 1, 0 ) 
     as Correct_Number_of_Guesses
  FROM 
     Table1 t1
       cross join 
     Table2 t2

Upvotes: 0

Related Questions