Dan,
You don't need a LEFT JOIN here. Left join lets you keep all of the rows in
one table in a
join, even when there are no matching rows in the second table.
You do have to use the group_members table twice, once to find all the
groups to which
Jim belongs, and again to find all of the members of those groups. Here's a
select
statement that does the trick.
SELECT G.NAME, M.name
FROM group_members A, group_members B, groups G, members M
where A.member_id = 1
and A.group_id = B.group_id
and G.id = B.group_id
and M.id = B.member_id
ORDER BY groups.name;
However, some comments on left joins...
Your proposed example has no WHERE clause, so ALL rows of group_members will
participate in the
join. The "group_members.member_id" in the ON clause only limits the rows
of groups which
will be used in the join, rather than perhaps contributing nulls. (That's
why you see a row for White.)
A brief description of left joins:
SELECT *
FROM table1 LEFT JOIN table2 ON join_condition;
returns:
(1) all rows returned by:
SELECT * from table1, table2 where join_condition;
(2) for each row in table1 that does NOT participate in (1), a row
from table1 with the table2 columns filled in with nulls
Then, you can add a WHERE clause to further restrict the returned rows; in
particular,
you can use table2.column IS NULL to get rows from table1 which do NOT match
table2.
Note that the join_condition in the ON clause does not restrict what rows
from
table1 participate in the result--without a WHERE, you get at least one row
in the result for each
row of table1. The ON clause DOES affect what rows of table2 get joined to
rows
of table1.
HTH,
Bill
> Date: Wed, 29 Oct 2003 11:57:54 -0600
> To: [EMAIL PROTECTED]
> From: Dan Hansen <[EMAIL PROTECTED]>
> Subject: More LEFT JOIN Headaches
> 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]
