Thread: need suggestions on sql query
07-15-2009, 03:26 PM #1Web Hosting Master
- Join Date
- Feb 2002
- New York
need suggestions on sql query
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);
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.
just a programmer
07-15-2009, 04:08 PM #2Newbie
- Join Date
- May 2009
Switching to a 3 table structure for this information might make what you are trying to do much easier.
LeaderId (FK to Members.Id)
TeamId (FK to Teams.Id)
MemberId (FK to Member.Id)
TeamMembers is your Xref table that will allow you to query who played for what team when.
07-16-2009, 01:16 PM #3Junior Guru Wannabe
- Join Date
- May 2008
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
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
membership_roster = memberDan Hildebrandt, Developer / Support, WebGroup Media LLC. - http://twitter.com/cerb5
Cerberus Helpdesk 5.x is a CRM toolkit. Remember anything about anybody, swiftly reply to a flood of e-mail,
capture feedback organically, track time, flag opportunities, share tasks, and more. http://www.cerberusweb.com/
By 3okl in forum Programming DiscussionReplies: 3Last Post: 10-11-2008, 01:40 AM
By strikeboy in forum Programming DiscussionReplies: 7Last Post: 10-03-2008, 09:55 PM
By stealth666 in forum Programming DiscussionReplies: 4Last Post: 07-01-2006, 03:58 PM
By Slidey in forum Programming DiscussionReplies: 5Last Post: 03-07-2006, 05:12 AM
By summcat in forum Programming DiscussionReplies: 12Last Post: 03-22-2003, 10:28 PM