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
