|
| 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!!!
|
|