Home > Archive > PHP DB > February 2007 > Re: [PHP-DB] Searching many-to-many map tables
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Re: [PHP-DB] 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/
| |
| Steve McGill 2007-02-08, 3:58 am |
| 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" <dmagick@gmail.com> wrote in message
news:45CA6CA0.8020105@gmail.com...
> Steve McGill wrote:
>
> 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
>
>
>
> 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/
|
|
|
|
|