Re: Searching many-to-many map tables

you must have three tables user (have at least user_id field) group (have at least groupd_id field) user_group (have 2 fields: user_id and group_id, you can also have user_group_id - depends on your need) /* if you only need the user id */ select user_id from user_group where group_id in (1,2); /* if you need other info */ select UG.user_id, U.*, G.* from user_group UG left join user U on UG.user_id = U.userid left join group G on UG.group_id = G.group_id where UG.group_id in (1,2); hth, ~ John On 2/7/07, Steve McGill <steve@bluearena.com> wrote:

Hello, I am trying to find out how to search a many-to-many map table efficiently. I have an example table: user,user_group 1,1 1,2 2,1 3,2 I want to find out all the users who are a member of BOTH groups 1 AND 2. In this example, this would just be the user with id 1. Until now, I can either do this with multiple queries and using PHP array_intersect, or one really ugly MySQL query: select user, count(user_group) as num_groups_found from users_groups where group IN (1,2) GROUP BY user HAVING num_groups_found=2 i.e. narrows down the groups I'm looking for and makes sure that they are all found for a user It works quite reliably I think but it's such a rubbish query that I was hoping that somebody could teach me some syntax that is better. Many thanks in advance, Steve -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php

-- GMail Rocks!!!