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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
