title: Select matching to two columns of subquery
body: <p><strong>Background:</strong></p>
  
  <p>Given table t1 with fields A, B (and others):</p>
  
  <pre><code>DROP TEMPORARY TABLE IF EXISTS t1; 
  CREATE TEMPORARY TABLE t1 (ID INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, A varchar(255), B int, C varchar(40));
  
  INSERT INTO t1 (A, B, C)
  SELECT 'AA', 11, 100
  UNION ALL
  SELECT 'BB', 12, 200
  UNION ALL
  SELECT 'BB', 12, 201
  UNION ALL
  SELECT 'AA', 12, 300
  UNION ALL
  SELECT 'AA', 11, 101;
  
  -- ID  A  B  C
  --  1  AA 11 100
  --  2  BB 12 200
  --  3  BB 12 201
  --  4  AA 12 300
  --  5  AA 11 101
  </code></pre>
  
  <hr>
  
  <p><strong>GOAL: For a given combination of A and B,</strong> examine how many rows there are in t1, and then list all those rows (to understand what is same and what is different between those rows).</p>
  
  <p>(Finally, but beyond the scope of this question, will be writing queries to process <strong>some</strong> of the older rows, that are determined to be "obsolete" (replaced by the most recent row with given A and B). Its not safe to do so for ALL combinations of A and B, at this time. A definitive answer on "what combinations of A and B are safe to delete old version of" is not available to me - this is a legacy table which has associated with it many GBs of external files, most of which are no longer relevant to anyone. All those files have been backed up; I need to make a conservative proposal as to which files to remove, and how I determined those files.)</p>
  
  <p><strong>I've made temp table t2 with all distinct combinations of A and B</strong> (plus an ID, and a count of how many rows of each combo):</p>
  
  <pre><code>DROP TEMPORARY TABLE IF EXISTS t2; 
  CREATE TEMPORARY TABLE t2 (ID INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, A varchar(255), B int)
  SELECT COUNT(1) As Cnt, A, B FROM t1
  GROUP BY A, B
  ORDER BY Cnt DESC;
  </code></pre>
  
  <p>SELECT * FROM t2</p>
  
  <pre><code>-- ID  Cnt  A  B
  --  1    2  AA 11
  --  2    2  BB 12
  --  3    1  AA 12
  </code></pre>
  
  <hr>
  
  <p><strong>The query that I am having trouble writing:</strong></p>
  
  <p>In the actual data, there are hundreds of rows for some combinations. I am most interested in the combinations that have a high count, so I attempt to dump the rows of t1, based on the first row of t2:</p>
  
  <pre><code>SELECT * FROM t1
  WHERE A=
      (SELECT A from t2 LIMIT 1 OFFSET 0) AND
      B=
      (SELECT B from t2 LIMIT 1 OFFSET 0);
  </code></pre>
  
  <p>This gives error:</p>
  
  <pre><code>Error Code: 1137. Can't reopen table: 't2'
  </code></pre>
  
  <p>I presume that I should refer to the row I want from t2:</p>
  
  <pre><code>(SELECT A, B from t2 LIMIT 1 OFFSET 0)
  </code></pre>
  
  <p>And then make a nested query that uses this row twice, in the two places where columns A and B are used.  I am stuck on how to write this query. The basic idea in my head is:</p>
  
  <pre><code>SELECT * FROM t1
      WHERE A=t3.A AND B=t3.B IN
          (SELECT A, B from t2 LIMIT 1 OFFSET 0) AS t3;
  </code></pre>
  
  <p>(which is not valid SQL)</p>
  
  <p>NOTE: "OFFSET 0" is there because then I will change to other offset values, to examine other A-B combos.</p>
  
  <p>The goal is to see response:</p>
  
  <pre><code>-- ID  A  B  C
  --  1  AA 11 100
  --  5  AA 11 101
  </code></pre>
  
  <hr>
  
  <p>Or maybe this can be done with a <code>JOIN</code>, but I'm not sure how to do a JOIN using just one row of <code>t2</code>.</p>