Searching many-to-many map tables

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

Re: Searching many-to-many map tables

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

Re: Searching many-to-many map tables

------=_Part_11841_23322442.1170894894654
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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

------=_Part_11841_23322442.1170894894654--

Re: Searching many-to-many map tables

Thanks Chris,

By the way do you have problems with spammers getting your gmail address
from usenet?

The user_id is actually what I'm searching for dynamically, so my initial
query was relatively correct as it returns one row with user 1.

However, I wasn't familiar with the concept of self-joining, so many thanks
for that. Still damn ugly so I see. Strange as I would have thought my
problem isn't unique. Best practice seems to recommend using many-to-many
tables instead of flat-tables yet I've yet to find an explanation of how to
convert the flat-table query "select * from users where group_id_1=1 and
group_id_2=2" into a map-table query in a better way than I've done already.

Steve

"Chris" wrote in message
news:45CA6CA0.8020105@gmail.com...
> 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