Select all rows with multiple conditions in another table
You can get the result by using a combination of a WHERE, GROUP BY, and HAVING to return the products that have both the Book and Audio values.

select p.id, p.name
from products p
inner join properties t
  on p.id = t.product_id
where t.key = 'Format'
  and t.value in ('Book', 'Audio')
group by p.id, p.name
having count(distinct t.value) = 2;


See SQL Fiddle with Demo.  Basically, you'll place the values that you want in the WHERE clause - this would be the Book and Audio.  Then you'll GROUP BY the product.id and product.name these are unique to each product. Finally you will use the HAVING clause to count the distinct properties.value that are returned.  This should equal the number of items that you have in your WHERE clause. 

This could also be written using conditional aggregation inside of the HAVING:

select p.id, p.name
from products p
inner join properties t
  on p.id = t.product_id
group by p.id, p.name
having sum(case when t.key = 'Format' and t.value = 'Book' then 1 else 0 end) &gt; 0
  and sum(case when t.key = 'Format' and t.value = 'Audio' then 1 else 0 end) &gt; 0


See SQL Fiddle with Demo

If you want to include the Language key, then you can use conditional aggregation in the HAVING clause:

select p.id, p.name
from products p
inner join properties t
  on p.id = t.product_id
group by p.id, p.name
having 
  sum(case when t.key = 'Format' and t.value = 'Book' then 1 else 0 end) &gt; 0
  and sum(case when t.key = 'Format' and t.value = 'Audio' then 1 else 0 end) &gt; 0
  and sum(case when t.key = 'Language' and t.value = 'English' then 1 else 0 end) &gt; 0


See Demo