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:dread@stripped]
Sent: Wednesday, February 26, 2003 4:05 PM
To: M Wells
Cc: mysql@stripped
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, johndoe@stripped
> 2, jane doe, janedoe@stripped
> 4, inigo montoya, inigo@stripped
>
> Can anyone help me work out how to achieve this?
>
mysql> select * from members;
+----+---------------+--------------------------+
| id | name | email |
+----+---------------+--------------------------+
| 1 | john doe | johndoe@stripped |
| 2 | jane doe | janedoe@stripped |
| 3 | joe bloggs | joebloggs@stripped |
| 4 | inigo montoya | inigo@stripped |
+----+---------------+--------------------------+
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 | johndoe@stripped |
| 2 | jane doe | janedoe@stripped |
| 4 | inigo montoya | inigo@stripped |
+----+---------------+-------------------------+
3 rows in set (0.01 sec)
Regards,
--
Don Read dread@stripped
-- 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 <mysql-thread133385@stripped>
To unsubscribe, e-mail <mysql-unsubscribe-Jun.Han=arimacomm.com.cn@stripped>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
