Sir, you are trying to construct a many-to-many join. (One list has
many members, one member subscribes to many lists.) This is done with
a join table.
join_lists_members(list_id, member_id)
To get all the e-mail addresses for a given list name, use
SELECT m.email
FROM (lists AS l INNER JOIN join_lists_member AS j ON l.id = j.list_id)
INNER JOIN members AS m ON j.member_id = m.id
WHERE l.name = <some list name>;
This is a little complicated, but it is the standard way of doing it,
and it doesn't limit either lists or members.
Bob Hall
>Hi,
>
>I`m setting up a email system, the same type of system as Yahoh Groups,
>Topica etc.. I`m just in need of some advise as to how best to handle my
>tables. At the moment I have the following tables setup..
>
>Lists - Stores info about the list
>ListOwners - Stores all List Owners info
>Members - Stores all member info
>Archive - Stores all messages sent to the lists
>
>I am trying to figure out the best way to associate Members with Lists that
>they have signed up to, at the moment I can only think of 2 methods both of
>which wouldn`t be very good.
>
>First being to have a couple of fields in the Members table which would store
>the title of the list they signed up to, the problem with this is that they
>would be limited to how many lists they sign up to.
>
>Second method would to store in the Lists table the members ID but again this
>would be limited to however many rows I create.
>
>Another method I have just thought of would be to create a table called
>Subscribe and store the List name, Member ID and email address. But that
>might slow it down a bit when searching for the email address`s to send the
>actual message out.
>
>Any one else offer any thoughts on this.
>
>Thanks
>Ade
>
>---------------------------------------------------------------------
>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
Know thyself? Absurd direction!
Bubbles bear no introspection. -Khushhal Khan Khatak
---------------------------------------------------------------------
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
