title: MySQL distinct combination of values from cross reference
body: <p>I have been trying to find an efficient way to query data a certain way. My database is a simple MySQL off an external web server I don't have complete access to, and it's accessed through a web browser. Any queries I have tried have involved extremely large query results.</p>
  
  <p>General table layouts:</p>
  
  <p><code>Product</code> table with columns <code>{ID, Name}</code> (it has more columns but for the query I need they are not relevant)</p>
  
  <p><code>Types</code> table with columns <code>{ID, Name}</code></p>
  
  <p><code>Crossreference</code> table with columns <code>{ProductID, TypeID}</code> (1 product can have many types, and 1 type can have many products)</p>
  
  <p>Query results needed:</p>
  
  <pre><code>ProductName1, 
  ProductName2, 
  ProductName3, 
  group_concat(TypeName), 
  Count(TypeName)
  with COUNT(TypeName) &gt; 1
  </code></pre>
  
  <p>Where <code>typename</code> includes product names of the same type, and there are 2 or more matching types between each product. So <code>group_concat(typename)</code> would include matches between </p>
  
  <ul>
  <li><em>productname1</em> and <em>productname2</em>, </li>
  <li>a different match between 
  <em>productname1</em> and <em>productname3</em>, </li>
  <li>a different match between <em>productname2</em> and productname3, </li>
  <li>and maybe a match between <em>productname1</em>, <em>productname2</em>, and <em>productname3</em> comprising of 4 distinct types.</li>
  </ul>
  
  <p>And finally with a row output of values 'a', 'b', and 'c' for the products, with no extra outputs of  'b','c','a' or 'c', 'b', 'a', etc.</p>
  
  <p>I have had better results with only 2 product columns output to a temp table, then deleting the duplicates, but I can't seem to make it efficient for 3 columns, or even have it be efficient to gather the data with just a single query and no temp table.</p>