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.
Use a join:
SELECT count(u.name) FROM {users} u JOIN {node} n ON u.uid = n.uid WHERE n.type = 'blog';
Thanks Adam
your query is not work. above query return all row with type='blog'. what do I need is the number of users. look at this example: if user A create 5 blogs, user B creates 3 blogs, and ser C creates 0 blog, then: 1. original query with subquery support, returns: 2 (this is the right answer) 2. your query, return: 8.
currently, I solve this problem by take all row using DISTINCT then use "Drupal db count" to count the rows, but this is not efficient, because when my db contains 1.000 users then the results is 1.000 rows, what do I need only 1 row that contain "1000" value.