OK so I have a table called teams which is where all teams for a game are listed. Teams possibly get or loose members every month.

So like for example one month I may get one new member where another team leader gets 0 new team members and then the following month the other team leader may get 2 new team members (and loose one or two) while the other team has no changes.

So My teams table has the following
rowid | memername | teamleader | assign_date

where the assign_date colum is the date the user was assigned to the teamleader. Now the key is I want to be able to look at what team members a team leader had in September and then also view what team members they had in October

I am trying to use a query like this

SELECT teamleader = 'jsmith'
AND assign_date = ( SELECT max( assign_date )from teams);


when I run the query I get only one result since of course it is just pulling what ever the highest date is for this teamleader


So what suggestions would people have for storing which players get assigned to which teamleader and then also keep a historical view. now that I may have a player assigned to one team leader for one month and then 2 months dowjn the road he/she may be assigned to another teamleader. however I want the teamleaders to be able to go backwards and look at what team members they had for different months. so like a team lead will be able to select a month (i.e., july) and see which players where assigned to them.

thanks all

Switching to a 3 table structure for this information might make what you are trying to do much easier.


Table: Members
--------------
Id
Name
etc...

Table: Teams
------------
Id
Name
LeaderId (FK to Members.Id)
etc...


Table: TeamMembers
------------------
rowid
TeamId (FK to Teams.Id)
MemberId (FK to Member.Id)
StartDate
EndDate


TeamMembers is your Xref table that will allow you to query who played for what team when.

You're may want to do something different for the historical data; less of an RDBMS thing and more of a log thing. You can, of course, do the log as a table in the db, and you could even use the log for determining current team leads if you wanted, but it seems conceptually easier to have some information duplicated.

Now the key is I want to be able to look at what team members a team leader had in September and then also view what team members they had in October

For this, you can't just look at assign_date > August and assign_date < October, because that won't match members that were assigned prior to that but remain assigned. You're actually asking a complicated question: Show me the Members that were assigned to LeaderX on or before DateY where Member was not subsequently assigned to another Leader...

This may, in fact, be possible in the mysql, but you can do something like (not real SQL):
===select member from teams where leader='leaderX' and assigned_date<October group by member
should give you the list of all members that have been on this team, then a (not real php):
===foreach (members as member)
select leader from teams where member=member order by assigned_date desc limit 0,1
if leader=leaderX
membership_roster[] = member