Also be aware that, at least on MySQL, subqueries in a WHERE clause get
executed for every otherwise-matching record. Depending on your dataset
that could eat up a lot of time. Subqueries in a SELECT or FROM clause
are only executed once, so those are fine.
With a proper join against a weighting table, though, the subquery
shouldn't even be necessary.
--Larry Garfield
Ken Winters wrote:
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
