On dinsdag 2 december 2003 4:07 Skippy told the butterflies: > I must admit I'm pretty stumped here. I'm using MySQL 4.0.12. > > I have several tables with info, and one which servers as a > link between them (has id's that refer the id's in all the others). > > The info tables hold people(table 1), which get assigned to groups(2) > by directives(3) and each time they are [re]assigned they get a > level(4) increase (levels vary from 5->1). The central link > table is called advancements. > > I'm trying to pick one year Y (the year is in the directives > table) and count how many people from group G were at level L > that year. So I have [G]roup, [Y]ear and [L]evel as given parameters. > > My problems: > * I can't count for year=Y because I would miss people who > got a level increase in earlier years (they now count as > being at that level). So I thought I'd look for year<=Y. > * But this creates another problem: people who got more than > one level increase in years <= Y appear once for every level > change! So this increases the total count when it shouldn't. > * I can't add a condition level=L because this would miss > later levels that the person got. > > select person_id,level_id from directives d join advancements a on > (d.id=a.directive_id) where d.year<=2002 and group_id=1; > > This gives: > > > person_id | level_id | > ... > > 179 | 5 | > > 180 | 2 | > > 180 | 3 | > ... > 193 rows in set (0.02 sec) > > Notice how person 180 appears twice, once for level 2 and > once for level 3. This inflates the total row count (they > should really only be 179). > > How can I do this so that I only get 180 at level 2, and how > can I then apply a condition that will only show me those at level 2? > > I'm not even sure that I'm right about this join anymore. > Everything I tried either gives me the inflated results due > to duplicate persons, or I get the right amount but can't > filter properly for a certain level. > > -- > Skippy
If you want to select those who HAVE BEEN at level 2 in the yeaer Y, you can just do "level_id = 2". But I guess you want to know who currently IS at level 2 IN the year Y? In that case, when using a 4.0.x I guess there is no way you are going to do that. I tried and couldn't. I know though, that with a little more puzzling I could do it in 4.1.x since (as I heard) that one supports the so-called "subselects". For now, you'd be stuck with a little bit of some sort of scripting language to parse the results... i'm sorry. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
