title: MySQL Select both This and That
body: <p>The tables look like these:</p>
  
  <pre><code>Table: Shops
  
  ShopCode   ShopName
  --------   --------
  A          Aladdin
  B          Backstreet
  C          Clerk's Store
  D          Debs Tool
  
  Table: Sale
  
  ShopCode   Product
  --------   -------
  A          Hammer
  A          Thermometer
  A          Compass
  B          Eraser
  B          Hammer
  C          Thermometer
  C          Hammer
  D          Thermometer
  </code></pre>
  
  <p>Find the name of the shops which sells BOTH Hammer and Thermometer. The result table would be</p>
  
  <pre><code>ShopName   
  -------------- 
  Aladdin
  Clerk's Store
  </code></pre>
  
  <p>I thought the following query will work, but its returning empty set</p>
  
  <pre><code>mysql&gt; SELECT Shops.ShopName FROM Shops
      -&gt; JOIN Sale ON Shops.ShopCode=Sale.ShopCode
      -&gt; WHERE Sale.Product='Hammer' AND Sale.Product='Thermometer'
      -&gt; GROUP BY Shops.ShopCode;
  </code></pre>
  
  <p>Also tried with OR instead of AND, but not working (returning all the shops). What might be a possible solution?</p>
  
  <p>Just to make it little bit clear, I want to select the shops that have both the items(hammer and thermo), even though shop B sells hammer and D sells Thermometer, they will not be included. Only A and C which is selling both the items should be on the result</p>