[development] Creative querying

[Ken Winters]

select * from users where uid in (select uid from users_roles where  
rid in (4,5,6,7,8));

Note that I dropped the rid column from the results, but your query  
didn't provide all that useful results for it to start with (picked  
one).  If you actually need it then you may still need a join, and  
also might need something like group_concat.

- Ken Winters

On Nov 23, 2009, at 10:41 AM, Brian Vuyk wrote:

> 1.) We are including extra data from the users table (wasn't central  
> to the question, so I omitted it to simplify)
> 2.) How would you structure this exactly with subquery?
>
> Brian
>
> Ken Winters wrote:
>> On Nov 23, 2009, at 10:18 AM, Brian Vuyk wrote:
>>>
>>> SELECT DISTINCT u.uid, ur.rid FROM {users} u RIGHT JOIN  
>>> {users_roles} ur ON ur.uid = u.uid WHERE rid = 6 OR rid = 8 OR rid  
>>> = 5 OR rid = 7 OR rid = 4 GROUP BY uid;
>>>
>>> Brian
>>
>> 1) Why are you doing a join when all the info you are selecting is  
>> in the users_roles table?  If you don't need it for some other  
>> reason, problem solved.
>> 2) I've found it's generally better to use subqueries (where X in  
>> (select Y from Z)) rather than join and group.
>>
>> - Ken Winters
>

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.drupal.org/pipermail/development/attachments/20091123/5685a326/attachment.html