By the way do you have problems with spammers getting your gmail address
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.
"Chris" <[EMAIL PROTECTED]> wrote in message
> Steve McGill wrote:
>> I am trying to find out how to search a many-to-many map table
>> I have an example table:
>> 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
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php