title: SQL query. Joining column with itself, looks like should work but missing pairs
body: <p>I've simplified the database to make it easier. Here's the diagram:</p>
  
  <p><a href="https://i.stack.imgur.com/906LK.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/906LK.png" alt="enter image description here"></a></p>
  
  <p>We have products. Products belong to categories and have manufacturers. </p>
  
  <p>What I need is,  <strong>for every pair of categories, to find the count of manufacturers that have manufactured products of both categories.</strong></p>
  
  <p>So the result set needs to look like this (this is not actual data):</p>
  
  <p><a href="https://i.stack.imgur.com/eh8tl.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/eh8tl.png" alt="enter image description here"></a></p>
  
  <p><strong>This is what I've tried so far:</strong></p>
  
  <pre><code>SELECT phC1.category_id as Cat1 , PhC2.category_id as Cat2, COUNT(PhM.manufacturer_id)
  FROM
  product_has_category phC1, product_has_category phC2, product_has_manufacturer PhM
  WHERE
  PhM.product_id = phC1.product_id AND PhM.product_id=PhC2.product_id  AND PhC1.category_id &lt; phC2.category_id 
  group by PhC1.category_id, PhC2.category_id
  order by PhC1.category_id, PhC2.category_id
  </code></pre>
  
  <hr>
  
  <p>This looks to me like it should be right, but I'm getting less results than I should be. I'm missing some Category pairs. Am I approaching this wrong?</p>
  
  <p>Thank you</p>