Sub selection in db_select selectQuery Possible Duplicate:
  Is it possible to join dynamic tables (subqueries) using db_select()?  




I have this part of a query running in drupal with db_query.

LEFT JOIN (SELECT ogmk.gid, ogmk.etid, kompas.* FROM og_membership ogmk 
        INNER JOIN node kompas ON kompas.nid=ogmk.etid AND kompas.type='kompas'
        INNER JOIN publication_date pd ON pd.nid=kompas.nid
        WHERE pd.published_at &gt; :start AND pd.published_at &lt; :end AND kompas.status = 1
        ) ogm_kompas
        ON ogm_kompas.gid=og.gid


The left join should give records if they are available. If not the fields will be null.

How can I transform this to the db_select method with a selectQuery, so I can use the theme function for sorting and paging?

Thanks in advance for your answer.
I just found out that you can add a db_select as a table into the left join. So the solution for this will be:

$subselect = db_select('og_membership', 'ogmk');
$subselect-&gt;join('node', 'kompas', "kompas.nid=ogmk.etid AND kompas.type='kompas'");
$subselect-&gt;join('publication_date', 'pd', 'pd.nid=kompas.nid');
$subselect-&gt;condition('pd.published_at', $end, '&lt;');
$subselect-&gt;condition('pd.published_at', $start, '&gt;');
$subselect-&gt;condition('kompas.status', 1, '=');
$subselect-&gt;fields('ogmk', array('gid', 'etid'));


$select-&gt;leftJoin($subselect, 'ogm_kompas', 'ogm_kompas.gid=og.gid');