How to determine if two users can transmit some information without mutliple requests

Question

I'm trying to figure out how to allow the user, [V], to visit another user profile, [A], to see all the groups that user [A] is in, as well as which group of them are both parts.

Tables:

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 make a general request for all user [A] groups 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 

This, of course, will 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 I want [V] to be able to see which groups they share and which they can join.

For instance:

 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 about this, using a subquery for each row returned, but I'm sure there is a simpler and more efficient way to do this using another connection or WHERE IN, but so far everything that I tried does not work.

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 does not 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 

Answer 2

 SELECT A.group_name, (u.user_id is not null) as AlreadyAMember FROM ( SELECT groups.group_id, groups.group_name FROM groups JOIN group_users ON groups.group_id=group_users.group_id WHERE group_users.user_id= $A LIMIT 0,10 ) A LEFT JOIN group_users u ON u.group_id=A.group_id and u.user_id= $V 

AlreadyAMember column is a boolean type (true / false)