Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2002
    Location
    New York
    Posts
    791

    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
    PHP Code:
    SELECT teamleader 'jsmith'
    AND assign_date = ( SELECT maxassign_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
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    just a programmer

  2. #2
    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.

  3. #3
    Join Date
    May 2008
    Posts
    43
    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
    Dan 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/

Similar Threads

  1. Get query from another query
    By 3okl in forum Programming Discussion
    Replies: 3
    Last Post: 10-11-2008, 01:40 AM
  2. sql query help
    By strikeboy in forum Programming Discussion
    Replies: 7
    Last Post: 10-03-2008, 09:55 PM
  3. sql query help
    By stealth666 in forum Programming Discussion
    Replies: 4
    Last Post: 07-01-2006, 03:58 PM
  4. sql 'or' in query
    By Slidey in forum Programming Discussion
    Replies: 5
    Last Post: 03-07-2006, 05:12 AM
  5. query
    By summcat in forum Programming Discussion
    Replies: 12
    Last Post: 03-22-2003, 10:28 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •