Dynamic query with left join I need to collect comments that belong to certain user and submitted on pages of certain content type nodes. Currently I'm making two queries:
      $nodes = db_select('node', 'n')
      -&gt;fields('n', array('nid'))
      -&gt;execute()-&gt;fetchAll();
      unset($node_list);
      foreach ($nodes as $node) {
        $node_list[] = $node-&gt;nid;
      }

      $comments = db_select('comment', 'c')
      -&gt;fields('c', array('cid', 'nid', 'created'))
      -&gt;condition('c.nid', [$node_list], 'IN')
      -&gt;execute()-&gt;fetchAll();
      foreach ($comments as $comment) {
        some actions
      }

and it's working fine. However, I believe I can make just one query for comment table by using some kind of database join to node table and check if a node belongs to $content_type. How can I do that?
Here is the documentation on joins which should put you on the right path to answering the question.
Untested, but something like this should work:
$query = db_select('node', 'n');
$query-&gt;join('comment', 'c', 'n.nid = c.nid');
$query
  -&gt;fields('n', array('nid', 'title'))
  -&gt;condition('n.type', 'page')
  -&gt;condition('n.status', '1')
  -&gt;orderBy('n.created', 'DESC')
  -&gt;addTag('node_access');