Steve McGill 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
Where's your userid check? You should be able to add that in as well.
select user, count(user_group) as num_groups_found from users_groups
where user='1' and 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.
The problem is you want two values from the same table (group is '1' or
'2'), so either you need to do the above or a self-join (as far as I
know anyway!) :/
--
Postgresql & php tutorials
http://www.designmagick.com/
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
