On Mon, 18 Oct 2004 17:49:22 -0400, Timothy Luoma <[EMAIL PROTECTED]> wrote: > > Ok, this makes a lot of sense now. (As usual, what seems like more > work initially pays off in the end.) > > Here's a specific question. > > The parent project is called "TiM". We will, at times, want to pull > out information for *everyone*. But more often we will want to pull > out information just from sub-projects. One of these is "FPP" and > another is "WW". > > WW right now has only taken place once and did not have any further > sub-divisions. > > FPP will span about 5 years, and be divided into 3 groups (and possibly > more after the initial pilot project). > > The people who are in the first group of FPP participants are referred > to as "FPP1", the second are "FPP2" (FPP3 has not started yet). > > FPP1 involves 28 people broken down into 4 groups (FPP11, FPP12, FPP13, > FPP14). > > FPP2 involves 19 people broken down into 3 groups (FPP21, FPP22, FPP23). > > I want to be able to match all those who are in the DB (i.e. "any TiM > participant"), or just those who are in FPP, or just those who are in > FPP1, or just those who are in FPP11 (or FPP23, etc). > > I'm having trouble figuring out how to be that specific and that broad > at the same time. Just not quite getting my head wrapped around it. > > TjL > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > >
Here is a way that'll allow you to have a single participant in more than one group, any number of groups in a sublevel, and any number of levels deep: table USERS: userid=15 userid=16 table groups_to_users: userid=15 ugid=10 userid=15 ugid=11 userid=16 ugid=12 table group_defs: ugid=10 level=0 id='FPP1' ugid=10 level=1 id='1' ugid=11 level=0 id='FPP2' ugid=11 level=1 id='1' ugid=11 level=2 id='2' ugid=12 level=0 id='WW' The above means user 15 is part of: FPP1-1 and FPP2-1-2 User 16 is part of: WW Now of course there is a better solution if you don't need that much flexibility as this example would require multiple inserts to add someone to a single group and processing after a select to put it together. To get a raw list of groups you'd use: select ugid, level, id from group_defs group by ugid, level; On the above table this should give you: 10 0 FPP1 10 1 1 11 0 FPP2 11 1 1 11 2 2 12 0 WW To get a formatted list of group names use: select ugid, group_concat(id order by level) from group_defs group by ugid; This will give you: 10 FPP11 11 FPP212 12 WW -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
