title: Mysql JOIN multiple tables and select mulitple values
body: <p>First of all I want to apologize for my bad English. I have a problem with multiple tables.
  I'm not exactly a newby in the mySQL world but I cannot figure out a solution for this problem. 
  For this problem I use 4 tables.</p>
  
  <ol>
  <li>Categories</li>
  <li>Products</li>
  <li>Specifications</li>
  <li>Specificationvalues</li>
  </ol>
  
  <p>Every category has specifications and products and every specificationvalues has products and specification id's.
  Now the user can make a selection with different values. 
  This is where my problem is.
  When the user selects value "green" and legs "4" I want all green products with 4 legs.
  So I used a JOIN (all kinds i think) to select the right product (example below)</p>
  
  <pre><code>SELECT DISTINCT products.id 
  FROM products 
    LEFT JOIN specificationvalues ON specificationvalues.products_id = products.id 
    LEFT JOIN specifications ON specificationvalues.specifications_id = specifications.id 
  WHERE specifications.name='materiaal' 
    AND specifications.name='kleur' 
    AND specificationvalues.id='77' 
    AND specificationvalues.id='78'
  </code></pre>
  
  <p>The problem is that all the values are in separate rows. That's why the WHERE doesn't work. I do not get MySQL error's. Only that it returns 0 rows.</p>
  
  <p>I hope someone can help me! I got a lot of good things from this forum so I hope it will help me again!</p>
  
  <hr>
  
  <p>I don't know why my changes yesterday where not saved. But here is my data again:</p>
  
  <pre><code>SPECIFICATIONS Table
  ID   CATEGORIES_ID     NAME
  38   297               Material
  39   297               Measures
  40   297               Color
  
  SPECIFICATIONVALUES Table
  ID   SPECIFICATIONS_ID  PRODUCTS_ID   VALUE
  1    38                 988979        Masive wood 
  2    39                 988979        24x57x98
  3    40                 988979        Yellow
  4    40                 988980        Black
  5    39                 388980        24x57x98
  
  
  PRODUCTS Table
  ID         NAME
  988979     Table
  988980     Chair
  </code></pre>
  
  <p>So now I want all black prducts with measure 24x57x98. I hope you can help me!</p>