locked
SQL stored procedure to find the number of people within a group of a particular year RRS feed

  • Question

  • I am wanting to create a stored procedure that accepts the team name and year and returns the number of players in that particular team for that particular year. I am working with MYSQL and listed below are the tables I am using:

    Person     (personID, name, phone, email, year)
    Player    
    (personID, dateOfBirth, school)
    playerTeam
    (personID, teamID)
    Team      
    (teamID, teamName, ageGroup)

    So far I have tried joining the tables and couting the personID only where teamName and Person.year = the input parameters but to no avail. Any ideas?

    Wednesday, May 23, 2012 4:56 AM

Answers

  • This is SQL Server forum not MySQL.. but can you try

    SELECT <columns> FROM Person P JOIN PayerTeam PT

    ON P.PersonID=PT.PersonID join Team T ON T.TeamID=PT.TeamID

    WHERE Year=@Year AND teamname =@TeamName


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    • Proposed as answer by Dimitri C Wednesday, May 23, 2012 6:49 AM
    • Marked as answer by amber zhangEditor Wednesday, May 30, 2012 8:10 AM
    Wednesday, May 23, 2012 6:45 AM

All replies

  • This is SQL Server forum not MySQL.. but can you try

    SELECT <columns> FROM Person P JOIN PayerTeam PT

    ON P.PersonID=PT.PersonID join Team T ON T.TeamID=PT.TeamID

    WHERE Year=@Year AND teamname =@TeamName


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    • Proposed as answer by Dimitri C Wednesday, May 23, 2012 6:49 AM
    • Marked as answer by amber zhangEditor Wednesday, May 30, 2012 8:10 AM
    Wednesday, May 23, 2012 6:45 AM
  • select<ColumnName>  from Person per
    inner join playerName ply on ply.PersonID = per.PersonID
    inner join Team tm on tm.teamID = ply.teamID
    where per.years =<<Year>> and tm.TeamName = <<TeamName>>

    Please insert the team name in the team table and join the Team name in the where condition.

    select<ColumnName> per.PersonID from Person per
    inner join playerName ply on ply.PersonID = per.PersonID
    inner join Team tm on tm.teamID = ply.teamID
    where per.years =2005 and tm.TeamName = 'a'


    With Thanks and Regards Sambath

    Wednesday, May 23, 2012 7:34 AM