Your adding the subquery via a join. you want the subquery to be run as piece of the outermost Select statement, which Drupal 7 SelectQuery cannot do see:
http://stackoverflow.com/questions/20214014/how-do-i-place-a-sub-query-in-a-select-statement-in-drupal7https://stackoverflow.com/questions/20214014/how-do-i-place-a-sub-query-in-a-select-statement-in-drupal7
The easiest way to do this to use db_query (a string) or maybe hook_query_alter after adding a tag to the db_select query and adding in the subquery via a string later. If you need additional info passed in and out the hook_query_alter then use the metadata options.
