larry white wrote:
Drupal Indonesia wrote:
In Drupal installation that using MySQL without subquery support, what is the right sql statement for this query?
SELECT count(u.name) FROM {users} u WHERE u.uid IN (SELECT uid from {node} n where type='blog')
Above query is show number of users who whas written Blog.
$inner_query="SELECT uid from {node} n where type='blog'";
$blog_uids_res=db_query($inner_query);
while ($blog_uids=db_fetch_object($blog_uids_res)) { $blog_uids_array[]=$blog_uids->uid; }
$blog_uids_where_str=implode(',',$blog_uids_array);
$outer_query="SELECT count(u.name) FROM {users} u WHERE u.uid IN ".$blog_uids_where_str;
$blog_user_count=db_result(db_query($outer_query));
doh. slight correction. the where condition for 'IN' must be inside parentheses. a couple of ways to do this (one or the other, not both):
1). change:
$blog_uids_where_str=implode(',',$blog_uids_array);
to
$blog_uids_where_str='('.implode(',',$blog_uids_array).')';
or
2). change:
$outer_query="SELECT count(u.name) FROM {users} u WHERE u.uid IN ".$blog_uids_where_str;
to
$outer_query="SELECT count(u.name) FROM {users} u WHERE u.uid IN (".$blog_uids_where_str.")";
-- larz