title: MYSQL Select Permutations; Require unique values across row
body: <p>I want to perform a select resulting in a set of <strong>permutations, not combinations</strong>. In other words I want to preform a query which returns rows with <strong>various quantities and arrangements</strong> of the items in my table. <strong>No ID may be repeated horizontally within the same row</strong>. </p>
  
  <p>For example we could have:</p>
  
  <pre><code>9 pick 3 
  9 pick 5 
  9 pick 7
  </code></pre>
  
  <p>I have a working query, but I'm looking to optimize it. The <code>WHERE...AND...</code> clause filters out rows with duplicate IDs. While this works, it requires several additional <code>AND</code> clauses for each pick (column) added. <strong>For <code>9 pick 6</code> Fifteen <code>!=</code> assertions are required!</strong> This can become unwieldy as the number of items picked increases. It also looks very bloated. I'm looking for something simple and elegant.</p>
  
  <p>Here's my table:</p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS `table1` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `col1` varchar(24) NOT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=30 ;
  
  INSERT INTO `table1` (`id`, `col1`) VALUES
  (1, 'Wool Hat'),
  (2, 'Wool Gloves'),
  (3, 'Fleece Sweater'),
  (4, 'Jean Pants'),
  (5, 'Cotton T-Shirt'),
  (6, 'Wool Socks'),
  (7, 'Wool Blanket'),
  (8, 'Cotton Boxers'),
  (9, 'Fleece Pants');
  </code></pre>
  
  <p>Here's my query:</p>
  
  <pre><code>SELECT * FROM 
  `table1` t1, 
  `table1` t2, 
  `table1` t3,
  `table1` t4,
  `table1` t5,
  `table1` t6
  
  WHERE
  t1.id != t2.id
  AND
  t1.id != t3.id
  AND
  t1.id != t4.id
  AND
  t1.id != t5.id
  AND
  t1.id != t6.id
  AND
  t2.id != t3.id
  AND
  t2.id != t4.id
  AND
  t2.id != t5.id
  AND
  t2.id != t6.id
  AND
  t3.id != t4.id 
  AND
  t3.id != t5.id 
  AND
  t3.id != t6.id 
  AND
  t4.id != t5.id
  AND
  t4.id != t6.id
  AND
  t5.id != t6.id
  
  ORDER BY t1.col1, t2.col1, t3.col1, t4.col1, t5.col1, t6.col1
  </code></pre>
  
  <p>Thanks for any insight!</p>
  
  <p>EDIT:</p>
  
  <p>The example is really irreverent. I'm just trying to accomplish the mathematical concept of Permutations in a simple and efficient way.</p>
  
  <p>By request, here's half of the expected results for <code>9 pick 2</code>. The other half is with the pairs switched.</p>
  
  <pre><code>Wool Hat + Wool Gloves
  Wool Hat + Fleece Sweater
  Wool Hat + Jean Pants
  Wool Hat + Cotton T-Shirt
  Wool Hat + Wool Socks
  Wool Hat + Wool Blanket
  Wool Hat + Cotton Boxers
  Wool Hat + Fleece Pants
  Wool Gloves + Fleece Sweater
  Wool Gloves + Jean Pants
  Wool Gloves + Cotton T-Shirt
  Wool Gloves + Wool Socks
  Wool Gloves + Wool Blanket
  Wool Gloves + Cotton Boxers
  Wool Gloves + Fleece Pants
  Fleece Sweater + Jean Pants
  Fleece Sweater + Cotton T-Shirt
  Fleece Sweater + Wool Socks
  Fleece Sweater + Wool Blanket
  Fleece Sweater + Cotton Boxers
  Fleece Sweater + Fleece Pants
  Jean Pants + Cotton T-Shirt
  Jean Pants + Wool Socks
  Jean Pants + Wool Blanket
  Jean Pants + Cotton Boxers
  Jean Pants + Fleece Pants
  Cotton T-Shirt + Wool Socks
  Cotton T-Shirt + Wool Blanket
  Cotton T-Shirt + Cotton Boxers
  Cotton T-Shirt + Fleece Pants
  Wool Socks + Wool Blanket
  Wool Socks + Cotton Boxers
  Wool Socks + Fleece Pants
  Wool Blanket + Cotton Boxers
  Wool Blanket + Fleece Pants
  </code></pre>
  
  <p>This excludes the following:</p>
  
  <pre><code>Wool Hat + Wool Hat
  Wool Gloves + Wool Gloves
  Fleece Sweater + Fleece Sweater
  Jean Pants + Jean Pants
  Cotton T-Shirt + Cotton T-Shirt
  Wool Socks + Wool Socks
  Wool Blanket + Wool Blanket
  Cotton Boxers + Cotton Boxers
  Fleece Pants + Fleece Pants
  </code></pre>