title: Mysql select entry from parent table with spacific entries in child table manay to many relation
body: <p>I have a Product table that has a many to many relation with product properties table. Now I want to select a product that has three product properties.</p>
  
  <p>Here is my schema </p>
  
  <p><a href="https://i.stack.imgur.com/g3v4Z.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/g3v4Z.png" alt="enter image description here"></a></p>
  
  <p>And here is my data </p>
  
  <ol>
  <li>Product </li>
  </ol>
  
  <p><a href="https://i.stack.imgur.com/GWgp1.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/GWgp1.png" alt="Product table id, name and group_id"></a></p>
  
  <ol start="2">
  <li>Property</li>
  </ol>
  
  <p><a href="https://i.stack.imgur.com/cL0gN.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/cL0gN.png" alt="Product property table id, name and type "></a></p>
  
  <ol start="3">
  <li>product_property</li>
  </ol>
  
  <p><a href="https://i.stack.imgur.com/o6G2q.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/o6G2q.png" alt="product to property table"></a></p>
  
  <p>I want to get product id that has property 1,4 and 7 e.g. product 1. 
  Or I wan to search product that has property 2, 5 and 7</p>
  
  <p>I have tried inner join and groups but can't find a solution. </p>
  
  <pre><code>select * from product as p join product_property as pp on p.id = pp.product_id join property as pt on pp.property_id = pt.id
  </code></pre>
  
  <p>I have created a sample at sqlfiddle here for testing. 
  <a href="http://sqlfiddle.com/#!9/aee4f" rel="nofollow noreferrer">http://sqlfiddle.com/#!9/aee4f</a></p>
  
  <p><strong>Update</strong>
  I managed to find solution using Exists but I want some elegant solution </p>
  
  <pre><code>select * from product as p where exists (select * from product_property pp where pp.property_id=1 and p.id=pp.product_id) and exists (select * from product_property pp where pp.property_id=4 and p.id=pp.product_id ) and exists (select * from product_property pp where pp.property_id=7 and p.id=pp.product_id);
  </code></pre>