db_select sub query

Question

I need to use sub query on db_select refer the SQL below.

I think the first select should be like db_select('tabe1','tab') but this table is a virtual table. I have to create temporary only for combining 2 columns with coalesce.

Normal SQL(that works)

select nid, field_title_value, field_state_value, newid, e.denomination_ed
from (SELECT nid, n.created, field_title_value, field_state_value, n.uid, 
edi.field_edit_target_id, COALESCE(edi.field_edit_target_id, n.uid) AS newid FROM node as n
left join field_data_field_state as et on n.nid = st.entity_id 
left join field_data_field_title as ta on n.nid = t.entity_id
left join field_data_field_edit as edi on n.nid = edi.entity_id) as tabe1
left join editor as e on e.uid = tabe1.newid

How to do the first db_select

Answer 1

This is example for subquery

    $subQuery = db_select('users','u');
    $subQuery->fields('u',array('uid'));

    $query = db_select('club_members','cm')->distinct();
    $query->fields('cm',array('country_id','name', 'country'));
    $query->condition('cm.status',1);
    $query->condition('cm.user_id',$subQuery,'NOT IN');
    $resultLow = $query->execute()->fetchAllAssoc('uid');

Answer 2

You can try the code:

$query = db_select('node', 'n')
  ->fields('n', array('nid'));
$subquery = db_select('node', 'n2')
  ->fields('n2', array('nid'))
  ->join('users', 'u', 'n2.uid = u.uid'
  ->condition('u.status', 0);
$query->condition('', $subquery, 'EXISTS');
$result = $query->execute();