Nested select with Drupals DataBase interface I have the following SQL query and need to translate it to the drupal API:

SELECT
  *,
  (SELECT COUNT(*)
    FROM newsletter
    WHERE email = pupils.email
  ) AS newsletter_flag
FROM 
  memberdata.pupils
LEFT JOIN
  memberdata.pupil_profiles
ON
  memberdata.pupils.id = memberdata.pupil_profiles."pupils.id"
WHERE
  pupils.status != 'u'
ORDER BY
  pupils.timestamp DESC
LIMIT 10;


Background: There is a table with pupils (some basic info including mail adress) and a table newsletter containing the email addresses of pupils that registered for the newsletter. I need a list of all pupil with their fields and additionally the field newsletter_flag, indicating if the pupils email adress is in the newsletter table or not. Additionally I need a join to another table with primary foreign key. I've come this far:

$query = Database::getConnection('default', 'for_migration')
-&gt;select('memberdata.pupils', 'pupils')
-&gt;fields('pupils', array('id', 'firstname', 'lastname', 'birthday', 'gender'))
-&gt;condition('status', 'u', '!=')
-&gt;range(0, 10)
-&gt;orderBy('pupils.timestamp', 'DESC');

$alias = $query-&gt;leftjoin('newsletter', 'nl', 'pupils.email = nl.email');
$query-&gt;addExpression("COUNT($alias.email)", 'newsletter_flag');


But it does not what I want (the pupil_profiles join is still missing of course, but the newsletter flag is the bigger problem I guess). The source database is a PostgreSQL one and the query itself is part of a Migration subclass, in which a MigrateSourceSQL is used as source. Therefore the query needs to be a SelectQueryInterface.
Ok, figured it out but do not like it:

$query = Database::getConnection('default', 'for_migration')
   -&gt;select('memberdata.pupils', 'p')
   -&gt;fields('p', array('id', 'firstname', 'lastname', 'birthday', 'gender'))
   -&gt;condition('status', 'u', '!=')
   -&gt;range(0, 10);
$query-&gt;addExpression("(SELECT COUNT(*) FROM newsletter WHERE email = p.email)", 'newsletter_flag');


There should be a smoother way for the newsletter_flag problem.