On Mon, 2009-11-23 at 10:18 -0500, 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;
If the order is an arbitrary order, you'll have somewhere to create a new table, containing rid and an order column, then join it with rid in your request, and finally order with the order field. SELECT DISTINCT(u.uid), ynst.rid FROM {users} JOIN {users_roles} ur ON ur.uid = u.uid JOIN {you_new_shiny_table} ynst ON nst.rid = ur.rid ORDER BY ynst.order DESC GROUP BY u.uid If this does not works (I did not tested it, I think the GROUP BY clause will override the ORDER BY one, maybe with more than one ON clause it may work) try a subquery in the result clause: SELECT DISTINCT(u.uid), (SELECT ynst.rid FROM {you_new_shiny_table} ynst WHERE r.rid = ur.rid ORDER BY ynst.order DESC LIMIT 1 ) AS rid FROM {users} JOIN {users_roles} ur ON ur.uid = u.uid GROUP BY u.uid Note: you can even easily create somewhere a form to order your roles (or altering the existing one) to add a dragtable to order them. Pierre.