Commerce: What field to join line_item in order to get product price in custom query?
As you can see in the graph from the question you're linking, there's a field that relates the line item with the products, that's not a database field but a Drupal field. This means that there will be a database table for the field data and revisions and you don't want to deal with that in a db_query, you should be using Entity Field Query instead that will know the database tables and fields to use in each case.

I'm guessing you want to get products from the line items you already have, or the order.

I'd get the line items with EntityFieldQuery:

$query = new EntityFieldQuery();
$result = $query
  -&gt;entityCondition('entity_type', 'commerce_line_item')
  -&gt;propertyCondition('order_id', &lt;-your-order-id-&gt;)
  -&gt;execute();


Now you've got the line items there, wrap them and get the product price:

if (!empty($result['commerce_line_item'])) {
  foreach ($result['commerce_line_item'] as $id =&gt; $value) {
    $line_item_wrapper = entity_metadata_wrapper('commerce_line_item', $id);
    $product_wrapper = $line_item_wrapper-&gt;commerce_product;
    $price = commerce_price_wrapper_value($product_wrapper, 'commerce_price', TRUE);
  }
}


Also, all this data is exposed to views, so you can create a view instead.