Don't like ads? PRO users don't see any ads ;-)
Public Pastes
Guest

Untitled

By: a guest on May 5th, 2012  |  syntax: None  |  size: 0.89 KB  |  hits: 13  |  expires: Never
download  |  raw  |  embed  |  report abuse  |  print
Text below is selected. Please press Ctrl+C to copy to your clipboard. (⌘+C on Mac)
  1. I need to compare results from two diferent tables to get the number of occurrence
  2. SELECT userID
  3.      , Case When t1.number1 = t2.number1 Then 1 Else 0 End +
  4.           Case When t1.number2 = t2.number2 Then 1 Else 0 End +
  5.           Case When t1.number3 = t2.number3 Then 1 Else 0 End +
  6.           Case When t1.number4 = t2.number4 Then 1 Else 0 End +
  7.           Case When t1.number5 = t2.number5 Then 1 Else 0 End +
  8.           Case When t1.number6 = t2.number6 Then 1 Else 0 End as CorrectGuesses
  9.   FROM Table1 t1
  10.   cross join Table2 t2
  11.        
  12. SELECT
  13.      userID,
  14.       if( t1.number1 = t2.number1, 1, 0 )+
  15.       if( t1.number2 = t2.number2, 1, 0 )+
  16.       if( t1.number3 = t2.number3, 1, 0 )+
  17.       if( t1.number4 = t2.number4, 1, 0 )+
  18.       if( t1.number5 = t2.number5, 1, 0 )+
  19.       if( t1.number6 = t2.number6, 1, 0 )
  20.      as Correct_Number_of_Guesses
  21.   FROM
  22.      Table1 t1
  23.        cross join
  24.      Table2 t2
create a new version of this paste RAW Paste Data