<html><body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space; "><div>select * from users where uid in (select uid from users_roles where rid in (4,5,6,7,8));</div><div><br></div><div>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.</div><div><br></div><div>- Ken Winters</div><br><div><div>On Nov 23, 2009, at 10:41 AM, Brian Vuyk wrote:</div><br class="Apple-interchange-newline"><blockquote type="cite"> <div bgcolor="#ffffff" text="#000000"> 1.) We are including extra data from the users table (wasn't central to the question, so I omitted it to simplify)<br> 2.) How would you structure this exactly with subquery?<br> <br> Brian<br> <br> Ken Winters wrote:<br> <blockquote cite="mid:F1D65A65-1438-4F97-A450-7D765B64C12C@coalmarch.com" type="cite"> <div> <div>On Nov 23, 2009, at 10:18 AM, Brian Vuyk wrote:</div> <blockquote type="cite"> <div><font class="Apple-style-span" color="#000000"><br> </font>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;<br> <br> Brian<br> </div> </blockquote> </div> <br> <div>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.</div> <div>2) I've found it's generally better to use subqueries (where X in (select Y from Z)) rather than join and group.</div> <div><br> </div> <div>- Ken Winters</div> </blockquote> <br> </div> </blockquote></div><br></body></html>