Re: [PHP-DB] 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" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > 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: [PHP-DB] Searching many-to-many map tables
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 <[EMAIL PROTECTED]> 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!!!
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/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] 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
