How to use subquery in views where clause In my project user can created by profile2 module. When user is created i am assigning some role,initially user is block state. I have another user role ie.user app rover  who can approved these user and send activation link for active user.

I am using Mail Logger module for saving sending mail
Now i have created a view for user unapprove list 

    SELECT users.name AS users_name, users.uid AS uid, users.mail AS users_mail, 
    profile_users.pid AS profile_users_pid, users.created AS users_created, 'profile2' AS 
    field_data_field_cc_first_name_profile2_entity_type, 'profile2' AS 
    field_data_field_cc_address_office_profile2_entity_type, 'profile2' AS 
    field_data_field_cc_designation_profile2_entity_type
         FROM 
         {users} users
         LEFT JOIN {profile} profile_users ON users.uid = profile_users.uid AND 
         profile_users.type IN ( 'company', 'goverment_profile' )
         WHERE (( (users.status = '0') AND (users.login = 0) AND (profile_users.type IN 
            ('company', 'goverment_profile')) ))
            ORDER BY users_created DESC
            LIMIT 10 OFFSET 0


Hear i want to add sub-query in where condition 
user those already send activation link who will not list in view.

My other table is mail_logger.

Is there any module by which i can use sub-query in where condition?
Just ran into the same question, here is what I found:

You can add a sub query to your view using the hook_views_query_alter hook in a custom module (documentation). Inside the module you should add something like the following:

function your_module_views_query_alter(&amp;$view, &amp;$query) {
  if ( $view-&gt;current_display == '[display in question]'){
    $query-&gt;add_where_expression([group], [sub query]);
  }
}


documentation for $query->add_where_expression.  You can add whatever where clause you need using this function.

hook_views_query_alter should go in your_module.views.inc.  Make sure you declare hook_views_api in your_module.module, otherwise views wont know to look for the file. For example:

function your_module_views_api() { // declare your views api # here
  return array(
    'api' =&gt; 3,
  );
}