MYSQL : many-to-many problem
One approach (you will need to test this for performance if the dataset is large) is this:

Select `sc1`.`product_id`
  From `subcategory_product` As `sc1`
  Join (Select `product_id` From `subcategory_product` Where `subcategory_id` = 3) `sc3`
    On `sc1`.`product_id` = `sc3`.`product_id`
  Join (Select `product_id` From `subcategory_product` Where `subcategory_id` = 4) `sc4`
    On `sc1`.`product_id` = `sc4`.`product_id`
  Where `subcategory_id` = 1
;


Basically, you're scanning the subcategory_product table once for each of the values, and then doing an INNER JOIN on the results to only get rows back where all three exist.