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" <dmagick@gmail.com> 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/