Why do I get PDOException SQLSTATE[42S22] Column not found 1054 Unknown column 'tn.tid' in 'on clause'"?
The error you get is caused by the fact you don't use tn as alias of any database table.

The code I would write is the following one.

$tid = arg(2);
$query = db_select('node', 'n');

$tn = $query-&gt;join('taxonomy_index', 'ti', '%alias.nid = n.nid');
$th = $query-&gt;join('taxonomy_term_hierarchy', 'th', "%alias.tid = $tn.tid");
$th1 = $query-&gt;join('taxonomy_term_hierarchy', 'th1', "%alias.tid = $th.parent");
$th2 = $query-&gt;join('taxonomy_term_hierarchy', 'th2', "%alias.tid = $th1.parent");

$query-&gt;condition('n.type', 'product')
  -&gt;condition(
    db_or()
      -&gt;condition("$tn.tid", $tid)
      -&gt;condition("$th1.parent", $tid)
      -&gt;condition("$th2.parent", $tid)
  )
  -&gt;fields('n', array('nid'))
  -&gt;extend('PagerDefault')
  -&gt;limit(1);


$query-&gt;join() is really doing an INNER join; if you need to make a LEFT OUTER join, then the code is the following.

$tid = arg(2);
$query = db_select('node', 'n');

$tn = $query-&gt;join('taxonomy_index', 'tn', '%alias.nid = n.nid');
$th = $query-&gt;addJoin('LEFT OUTER', 'taxonomy_term_hierarchy', 'th', "%alias.tid = $tn.tid");
$th1 = $query-&gt;addJoin('LEFT OUTER', 'taxonomy_term_hierarchy', 'th1', "%alias.tid = $th.parent");
$th2 = $query-&gt;addJoin('LEFT OUTER', 'taxonomy_term_hierarchy', 'th2', "%alias.tid = $th1.parent");

$query-&gt;condition('n.type', 'product')
  -&gt;condition(
    db_or()
      -&gt;condition("$tn.tid", $tid)
      -&gt;condition("$th1.parent", $tid)
      -&gt;condition("$th2.parent", $tid)
  )
  -&gt;fields('n', array('nid'))
  -&gt;extend('PagerDefault')
  -&gt;limit(1);


As side notes:


$query-&gt;join(), and $query-&gt;addJoin() returns the alias they used
In the conditions passed to $query-&gt;join(), and $query-&gt;addJoin() %alias is replaced with the table alias the method is using


SelectQuery::addJoin() avoids conflicts between aliases using the following code.

  $alias_candidate = $alias;
  $count = 2;
  while (!empty($this-&gt;tables[$alias_candidate])) {
    $alias_candidate = $alias . '_' . $count++;
  }
  $alias = $alias_candidate;

  if (is_string($condition)) {
    $condition = str_replace('%alias', $alias, $condition);
  }


Writing code similar to the one I used, you are sure not to cause any conflict between aliases, and it is useful in the case the code is changed successively, or part of the query is populated from a function.
In this case, the join part could have written as follows, and it could still work correctly.

$tn = $query-&gt;join('taxonomy_index', 'tn', '%alias.nid = n.nid');
$th = $query-&gt;join('taxonomy_term_hierarchy', 'th', "%alias.tid = $tn.tid");
$th1 = $query-&gt;join('taxonomy_term_hierarchy', 'th', "%alias.tid = $th.parent");
$th2 = $query-&gt;join('taxonomy_term_hierarchy', 'th', "%alias.tid = $th1.parent");