Re: Help needed with SQL...
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
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
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
RE: Help needed with SQL...
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! Thanks in advantage for help! -Original Message- From: Don Read [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 26, 2003 4:05 PM To: M Wells Cc: [EMAIL PROTECTED] Subject: RE: Help needed with SQL... On 26-Feb-2003 M Wells wrote: > Hi All, > > I'm having problems creating a query to return a recordset from the > following situation: > > I have two tables, one which contains member details, and the other > which contains details re: the groups to which the member belongs. > > When a member logs into a particular part of my site, I want to show > them the details of the other people who belong to the same groups they > do. > > So, if I passed the query the memberid value of '1', I'd like to return > a a unique records recordset of: > > 1, john doe, [EMAIL PROTECTED] > 2, jane doe, [EMAIL PROTECTED] > 4, inigo montoya, [EMAIL PROTECTED] > > Can anyone help me work out how to achieve this? > mysql> select * from members; ++---+--+ | id | name | email| ++---+--+ | 1 | john doe | [EMAIL PROTECTED] | | 2 | jane doe | [EMAIL PROTECTED] | | 3 | joe bloggs| [EMAIL PROTECTED] | | 4 | inigo montoya | [EMAIL PROTECTED] | ++---+--+ mysql> select * from groups; +---+--+ | groupname | memberid | +---+--+ | group1|1 | | group1|2 | | group2|1 | | group2|4 | | group3|3 | +---+--+ 5 rows in set (0.00 sec) 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; ++---+-+ | id | name | email | ++---+-+ | 1 | john doe | [EMAIL PROTECTED]| | 2 | jane doe | [EMAIL PROTECTED] | | 4 | inigo montoya | [EMAIL PROTECTED] | ++---+-+ 3 rows in set (0.01 sec) 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
RE: Help needed with SQL...
On 26-Feb-2003 M Wells wrote: > Hi All, > > I'm having problems creating a query to return a recordset from the > following situation: > > I have two tables, one which contains member details, and the other > which contains details re: the groups to which the member belongs. > > When a member logs into a particular part of my site, I want to show > them the details of the other people who belong to the same groups they > do. > > So, if I passed the query the memberid value of '1', I'd like to return > a a unique records recordset of: > > 1, john doe, [EMAIL PROTECTED] > 2, jane doe, [EMAIL PROTECTED] > 4, inigo montoya, [EMAIL PROTECTED] > > Can anyone help me work out how to achieve this? > mysql> select * from members; ++---+--+ | id | name | email| ++---+--+ | 1 | john doe | [EMAIL PROTECTED] | | 2 | jane doe | [EMAIL PROTECTED] | | 3 | joe bloggs| [EMAIL PROTECTED] | | 4 | inigo montoya | [EMAIL PROTECTED] | ++---+--+ mysql> select * from groups; +---+--+ | groupname | memberid | +---+--+ | group1|1 | | group1|2 | | group2|1 | | group2|4 | | group3|3 | +---+--+ 5 rows in set (0.00 sec) 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; ++---+-+ | id | name | email | ++---+-+ | 1 | john doe | [EMAIL PROTECTED]| | 2 | jane doe | [EMAIL PROTECTED] | | 4 | inigo montoya | [EMAIL PROTECTED] | ++---+-+ 3 rows in set (0.01 sec) 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
Help needed with SQL...
Hi All, I'm having problems creating a query to return a recordset from the following situation: I have two tables, one which contains member details, and the other which contains details re: the groups to which the member belongs. When a member logs into a particular part of my site, I want to show them the details of the other people who belong to the same groups they do. Let's say my Members table looks like this: memberid, membername, memberemail 1, john doe, [EMAIL PROTECTED] 2, jane doe, [EMAIL PROTECTED] 3, joe bloggs, [EMAIL PROTECTED] 4, inigo montoya, [EMAIL PROTECTED] And my Groups table looks like this: groupname, memberid group1, 1 group1, 2 group2, 1 group2, 4 group3, 3 So, if I passed the query the memberid value of '1', I'd like to return a a unique records recordset of: 1, john doe, [EMAIL PROTECTED] 2, jane doe, [EMAIL PROTECTED] 4, inigo montoya, [EMAIL PROTECTED] Can anyone help me work out how to achieve this? Many thanks in advance! Regards and best wishes, Murray Wells - 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
