title: Join One Table To Get Random Rows from 2nd Table
body: <p>I have two tables: #tmpParams, which lists 29 combinations of Disability and Grade. The 2nd table, #tmpSource contains many students with their StudentID, Disability, and Grade.  I need to select 29 random rows from #tmpSource, joining on #tmpParams by Disability and Grade.</p>
  
  <p>In other words, someone handed me a list of parameters for 29 students (Disability and Grade) and they want 29 random students from #tmpSource, including StudentID, matching on the two fields.</p>
  
  <p>This is the DDL:</p>
  
  <pre><code>  IF OBJECT_ID('tempdb..#tmpParams') IS NOT NULL 
          BEGIN
                DROP TABLE #tmpParams
          END
    GO
  
    IF OBJECT_ID('tempdb..#tmpSource') IS NOT NULL 
          BEGIN
                DROP TABLE #tmpSource
          END
    GO
  
      /* Create parameter table */
      CREATE TABLE #tmpParams
           ( 
              Disability        varchar(10)
              ,Grade      varchar(10)
           )
      GO        
  
      /* populate it */
      INSERT INTO #tmpParams
          (Disability, Grade)
      SELECT '13 - AUT' ,'Grade 1'  UNION ALL
      SELECT '08 - SL' ,'Grade 1'  UNION ALL
      SELECT '11 - PSD' ,'Grade 1'  UNION ALL
      SELECT '04 - SLD' ,'Grade 2'  UNION ALL
      SELECT '04 - SLD' ,'Grade 2'  UNION ALL
      SELECT '08 - SL' ,'Grade 2'  UNION ALL
      SELECT '08 - SL' ,'Grade 2'  UNION ALL
      SELECT '08 - SL' ,'Grade 2'  UNION ALL
      SELECT '14 - TBI' ,'Grade 2'  UNION ALL
      SELECT '07 - PD' ,'Grade 2'  UNION ALL
      SELECT '08 - SL' ,'Grade 3'  UNION ALL
      SELECT '08 - SL' ,'Grade 3'  UNION ALL
      SELECT '08 - SL' ,'Grade 3'  UNION ALL
      SELECT '08 - SL' ,'Grade 3'  UNION ALL
      SELECT '08 - SL' ,'Grade 3'  UNION ALL
      SELECT '07 - PD' ,'Grade 3'  UNION ALL
      SELECT '04 - SLD' ,'Grade 4'  UNION ALL
      SELECT '04 - SLD' ,'Grade 4'  UNION ALL
      SELECT '08 - SL' ,'Grade 4'  UNION ALL
      SELECT '08 - SL' ,'Grade 4'  UNION ALL
      SELECT '08 - SL' ,'Grade 4'  UNION ALL
      SELECT '08 - SL' ,'Grade 4'  UNION ALL
      SELECT '08 - SL' ,'Grade 4'  UNION ALL
      SELECT '04 - SLD' ,'Grade 5'  UNION ALL
      SELECT '04 - SLD' ,'Grade 5'  UNION ALL
      SELECT '04 - SLD' ,'Grade 5'  UNION ALL
      SELECT '04 - SLD' ,'Grade 5'  UNION ALL
      SELECT '08 - SL' ,'Grade 5'  UNION ALL
      SELECT '08 - SL' ,'Grade 5';
      GO
  
  
      /* Create table to hold source */
      CREATE TABLE #tmpSource
           ( 
              StudentID          int
              ,Disability     varchar(10)
              ,Grade          varchar(10)
           )
      GO 
  
      INSERT INTO #tmpSource
          (StudentID, Disability, Grade)
      SELECT '3', '04 - SLD', 'Grade 2'  UNION ALL
      SELECT '31', '04 - SLD', 'Grade 2'  UNION ALL
      SELECT '39', '04 - SLD', 'Grade 2'  UNION ALL
      SELECT '46', '04 - SLD', 'Grade 2'  UNION ALL
      SELECT '10', '04 - SLD', 'Grade 4'  UNION ALL
      SELECT '13', '04 - SLD', 'Grade 4'  UNION ALL
      SELECT '25', '04 - SLD', 'Grade 4'  UNION ALL
      SELECT '33', '04 - SLD', 'Grade 4'  UNION ALL
      SELECT '57', '04 - SLD', 'Grade 4'  UNION ALL
      SELECT '60', '04 - SLD', 'Grade 4'  UNION ALL
      SELECT '8', '04 - SLD', 'Grade 5'  UNION ALL
      SELECT '19', '04 - SLD', 'Grade 5'  UNION ALL
      SELECT '23', '04 - SLD', 'Grade 5'  UNION ALL
      SELECT '51', '04 - SLD', 'Grade 5'  UNION ALL
      SELECT '55', '04 - SLD', 'Grade 5'  UNION ALL
      SELECT '16', '07 - PD', 'Grade 2'  UNION ALL
      SELECT '28', '07 - PD', 'Grade 2'  UNION ALL
      SELECT '36', '07 - PD', 'Grade 2'  UNION ALL
      SELECT '43', '07 - PD', 'Grade 2'  UNION ALL
      SELECT '11', '07 - PD', 'Grade 3'  UNION ALL
      SELECT '14', '07 - PD', 'Grade 3'  UNION ALL
      SELECT '26', '07 - PD', 'Grade 3'  UNION ALL
      SELECT '34', '07 - PD', 'Grade 3'  UNION ALL
      SELECT '58', '07 - PD', 'Grade 3'  UNION ALL
      SELECT '61', '07 - PD', 'Grade 3'  UNION ALL
      SELECT '5', '08 - SL', 'Grade 1'  UNION ALL
      SELECT '21', '08 - SL', 'Grade 1'  UNION ALL
      SELECT '41', '08 - SL', 'Grade 1'  UNION ALL
      SELECT '48', '08 - SL', 'Grade 1'  UNION ALL
      SELECT '2', '08 - SL', 'Grade 2'  UNION ALL
      SELECT '30', '08 - SL', 'Grade 2'  UNION ALL
      SELECT '38', '08 - SL', 'Grade 2'  UNION ALL
      SELECT '45', '08 - SL', 'Grade 2'  UNION ALL
      SELECT '12', '08 - SL', 'Grade 3'  UNION ALL
      SELECT '15', '08 - SL', 'Grade 3'  UNION ALL
      SELECT '27', '08 - SL', 'Grade 3'  UNION ALL
      SELECT '35', '08 - SL', 'Grade 3'  UNION ALL
      SELECT '59', '08 - SL', 'Grade 3'  UNION ALL
      SELECT '62', '08 - SL', 'Grade 3'  UNION ALL
      SELECT '9', '08 - SL', 'Grade 4'  UNION ALL
      SELECT '20', '08 - SL', 'Grade 4'  UNION ALL
      SELECT '24', '08 - SL', 'Grade 4'  UNION ALL
      SELECT '52', '08 - SL', 'Grade 4'  UNION ALL
      SELECT '56', '08 - SL', 'Grade 4'  UNION ALL
      SELECT '7', '08 - SL', 'Grade 5'  UNION ALL
      SELECT '18', '08 - SL', 'Grade 5'  UNION ALL
      SELECT '22', '08 - SL', 'Grade 5'  UNION ALL
      SELECT '50', '08 - SL', 'Grade 5'  UNION ALL
      SELECT '54', '08 - SL', 'Grade 5'  UNION ALL
      SELECT '4', '11 - PSD', 'Grade 1'  UNION ALL
      SELECT '32', '11 - PSD', 'Grade 1'  UNION ALL
      SELECT '40', '11 - PSD', 'Grade 1'  UNION ALL
      SELECT '47', '11 - PSD', 'Grade 1'  UNION ALL
      SELECT '6', '13 - AUT', 'Grade 1'  UNION ALL
      SELECT '17', '13 - AUT', 'Grade 1'  UNION ALL
      SELECT '42', '13 - AUT', 'Grade 1'  UNION ALL
      SELECT '49', '13 - AUT', 'Grade 1'  UNION ALL
      SELECT '53', '13 - AUT', 'Grade 1'  UNION ALL
      SELECT '1', '14 - TBI', 'Grade 2'  UNION ALL
      SELECT '29', '14 - TBI', 'Grade 2'  UNION ALL
      SELECT '37', '14 - TBI', 'Grade 2'  UNION ALL
      SELECT '44', '14 - TBI', 'Grade 2';
      GO
  </code></pre>
  
  <p>That populates 29 students into #tmpParams and 62 into #tmpSource. My real-world scenario is, of course, much more complex.</p>
  
  <p>I can get 29 random students from #tmpSource easily enough...but how do I JOIN on #tmpParams to get 29 students that match on the two fields?</p>
  
  <pre><code>   SELECT TOP 29
         TS.StudentID
         ,TS.Disability
         ,TS.Grade
     FROM #tmpSource AS TS
     ORDER BY NEWID();
     GO
  </code></pre>
  
  <p>As always, thank you for your help, and please let me know if I can clarify something or make this easier to read.</p>