How to determine if two users share some information without mutliple queries

Question

I'm trying to work out how to allow a user, [V], visiting another user's profile, [A], to see all the groups that user [A] is in and also which of the groups they're both part of.

The tables are:

USERS TABLE
user_id | name  | email....
1       | Drent | drents... [V]
2       | Dude2 | dude2@... [A]
3       | Dude3 | dude3@...

GROUPS TABLE
group_id | group_name   | joining_policy
1        | The Crazies  | invite_only
2        | Team OSM     | open
3        | My Group     | approval_needed

GOUP_USERS TABLE
group_id | user_id
1        | 1
1        | 2
3        | 2
2        | 1
2        | 3

I can do a general query for all the groups user [A] is part of:

SELECT groups.group_name FROM groups JOIN group_users 
ON groups.group_id=group_users.group_id WHERE group_users.user_id=2 LIMIT 0,10

Which would of course return this:

The Crazies - <a href="$row['group_id']?join=$my_user_id>Join This Group</a>
My Groups - <a href="$row['group_id']?join=$my_user_id>Join This Group</a>

But what I want is a way for [V] to see which groups they share and which they can join

For example:

The Crazies - You're already a member
My Groups - <a href="$row['group_id']?join=$my_user_id>Join This Group</a>

At the moment I can only think of doing this using a subquery for each row returned but I'm sure there's an easier, more efficient way to do it using another join or a WHERE IN but so far everything I've tried hasn't worked.

Something like:

SELECT groups.group_name FROM groups JOIN group_users  
ON groups.group_id=group_users.group_id JOIN users AS visitor 
ON visitor.user_id=group_users.user_id WHERE group_users.user_id=2 LIMIT 0,10

but I know this doesn't work.

Any help would be greatly appreciated.

Answer 1

SELECT  g.*, guv.group_id IS NOT NULL AS is_member
FROM    group_users gua
JOIN    group g
ON      g.id = gua.group_id
LEFT JOIN
        group_users guv
ON      guv.group_id = gua.group_id
        AND guv.user_id = $v
WHERE   gua.user_id = $a