How to select FROM {commerce_product} p WHERE p.field_TERMFIELD = $given_tid and the resulted p.product_id is referenced on a given nid
I think I got it figured out:

$query = db_select('commerce_product', 'p');
$query-&gt;join('field_data_field_TERMFIELD', 't', 't.entity_id = p.product_id');
// This is the product reference field table. This field is attached to our node.
$query-&gt;join('field_data_field_product_reference', 'pr', 'pr.field_product_reference_product_id = p.product_id');

$query
  // This is the product_id we are looking for.
  -&gt;fields('p', array('product_id'))

  -&gt;condition('field_TERMFIELD_tid', $tid)
  -&gt;condition('pr.entity_type', 'node')
  -&gt;condition('pr.entity_id', $nid);

 $result = $query-&gt;execute();