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. <snip> > > 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
