Try something like this:
mysql> SELECT groups.name AS group_name, members.name AS username
-> FROM group_members AS g1, group_members AS g2, groups, members
-> WHERE g1.member_id=1 AND g2.group_id=g1.group_id
-> AND members.id=g2.member_id AND groups.id=g2.group_id
-> ORDER BY group_name;
+------------+----------+
| group_name | username |
+------------+----------+
| Blue | Jim |
| Blue | Mary |
| Blue | Bob |
| Red | Jim |
+------------+----------+
4 rows in set (0.00 sec)I won't promise that's the best way.
Michael
Dan Hansen wrote:
Please help -- by brain is fried...
I have three tables: groups, members, and a link table called group_members.
There is a record in group_members for each member that belongs to a given group.
A member may belong to several group.
I want to get a results set that shows all members where a given member, call him "Jim," also belongs.
Thus if Jim is a member of groups 1 and 3, Mary is a member of 3, and Bob belongs to 2 and 3, I want a results like the one below. I know how to get to this using an intermediate temporary table, but I'd like to vaid that if there's a way. The queries I have tried either give me all members in all groups, or only records where Jim is a member. My closest query follows, then a sketch of my tables with a results set at the end. Can someone please tell me where I an blowing it?
Thanks -
Dan Hansen
SELECT groups.name AS group_name, members.username AS username
FROM group_members
LEFT JOIN groups ON groups.id = group_members.group_id
AND group_members.member_id = 1 (This is Jim, it will be replaced by a variable in PHP code)
LEFT JOIN members ON members.id = group_members.member_id
ORDER BY groups.name
======================= members ----------------------- id name ----------------------- 1 Jim 2 Mary 3 Bob
======================= groups ----------------------- id name ----------------------- 1 Red 2 White 3 Blue
======================= group_members ----------------------- id member_id group_id ----------------------- 1 1 1 2 1 3 3 2 3 4 3 2 5 3 3
======================== DESIRED RESULT SET ------------------------ group_name username ------------------------ Red Jim Blue Jim Blue Mary Blue Bob
======================== DESIRED RESULT SET ------------------------ group_name username ------------------------ Red Jim Blue Jim Blue Mary Blue Bob (SHOULD NOT GET THIS ONE) White Bob
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
