It is usually called a self-join, and it is a very useful technique. You have to do this when you need to obtain or access two separate subsets from a single table and somehow use those in a join. In this case, you want the groupids a specific member belongs to (set one) as well as all members belonging to the same group (set two). And the two "instances" of the table have to be identified through use of table aliases.
Regards, Tore. ----- Original Message ----- From: "Sheryl Canter" <[EMAIL PROTECTED]> To: "Don Read" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Wednesday, February 26, 2003 10:56 AM Subject: Re: Help needed with SQL... > I've never seen opening a table twice and then doing a join back with > itself. Am I wording this correctly? Is that how to describe what you're > doing? > > Thanks for posting this to the full list. > > - Sheryl > > > ----- Original Message ----- > From: "Don Read" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Cc: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> > Sent: Wednesday, February 26, 2003 5:11 AM > Subject: RE: Help needed with SQL... > > > > On 26-Feb-2003 [EMAIL PROTECTED] wrote: > > Yes , I had use this query statement in my MySQL server, > > But I have a query about it. > > Why are you use "WHERE a.groupname=b.groupname AND members.id=b.memberid > > AND a.memberid=1"? > > Can you give me a explain or give me a advise! > > > > Sure. > > mysql> SELECT DISTINCT members.* FROM members, groups as a, groups as b > -> WHERE a.groupname=b.groupname AND members.id=b.memberid > -> AND a.memberid=1; > > > The 'a.memberid=1' clause looks up the groups that member 1 belongs to in > the > groups table (as a), giving 'group1' & 'group2'. > > Then it joins back against the groups table (as b) with the > 'a.groupname=b.groupname' clause to get the folks that belong to these > group(s). > > Finally the 'members.id=b.memberid' bit selects the records out of the > members > table, with the DISTINCT function suppressing any duplicates. > > Clear as mud? > > (I could've be a little more obvious if I'd put the 'a.memberid=1' clause > first. Sorry ...) > > Regards, > -- > Don Read [EMAIL PROTECTED] > -- It's always darkest before the dawn. So if you are going to > steal the neighbor's newspaper, that's the time to do it. > (53kr33t w0rdz: sql table query) > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
