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);

~ John

On 2/7/07, Steve McGill <[EMAIL PROTECTED]> wrote:


I am trying to find out how to search a many-to-many map table

I have an example table:


I want to find out all the users who are a member of BOTH groups 1 AND 2.
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,

PHP Database Mailing List (
To unsubscribe, visit:

GMail Rocks!!!

Reply via email to