[support] using drupal in mysql without subquery

[larry white]

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