By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
 424,847 Members | 2,153 Online
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,847 IT Pros & Developers. It's quick & easy.

# Tough Select Query

 P: n/a I am trying to create a query (in either sql or the design view) to determine which two (or more I suppose if it's not too complicated) baseball players were teammates the longest. The database includes the following fields: YearId, PlayerId, and teamId. I have been unable to write query that can caluclate the number of years players would have played together on a team. (Technical note: some players have records for more than one team for a given year because they may have been traded during the season; for the purpose of this analysis, I will assume that if two players each have a record for a partiucular team for a particular year, they were teammates). Thanks in advance for any help on this query. --Dan L. Jul 23 '06 #1
Share this Question
7 Replies

 P: n/a Dan wrote: I am trying to create a query (in either sql or the design view) to determine which two (or more I suppose if it's not too complicated) baseball players were teammates the longest. The database includes the following fields: YearId, PlayerId, and teamId. I have been unable to write query that can caluclate the number of years players would have played together on a team. (Technical note: some players have records for more than one team for a given year because they may have been traded during the season; for the purpose of this analysis, I will assume that if two players each have a record for a partiucular team for a particular year, they were teammates). Thanks in advance for any help on this query. This self-join may be what you're looking for: SELECT A.Player as Player1, A1.Player AS Player2, A.Team, Count(A.Year) AS [Teammate Years] FROM Table1 A INNER JOIN Table1 A1 ON (A.Team = A1.Team) AND (A.Year = A1.Year) WHERE A.Player < A1.Player GROUP BY A.Player, A1.Player, A.Team; Jul 23 '06 #2

 P: n/a DFS, Thanks for the ingenious solution. --Dan L. > This self-join may be what you're looking for: SELECT A.Player as Player1, A1.Player AS Player2, A.Team, Count(A.Year) AS [Teammate Years] FROM Table1 A INNER JOIN Table1 A1 ON (A.Team = A1.Team) AND (A.Year = A1.Year) WHERE A.Player < A1.Player GROUP BY A.Player, A1.Player, A.Team; Jul 24 '06 #3

 P: n/a As I have tested and experimetned withthis query I realized it only works for players who are teamates on one team. It doesn' t pick up guys who may have been teammates on more than one team. For example, Pete Rose and Tony Perez were teammates for 16 years on the Reds but also 1 year on the Phillies. Unfortunately, I am not good enough with sql to make the necessary adjustment ot the query. Any additional thoughts would be apreciated. --Dan L. DFS wrote: Dan wrote: I am trying to create a query (in either sql or the design view) to determine which two (or more I suppose if it's not too complicated) baseball players were teammates the longest. The database includes the following fields: YearId, PlayerId, and teamId. I have been unable to write query that can caluclate the number of years players would have played together on a team. (Technical note: some players have records for more than one team for a given year because they may have been traded during the season; for the purpose of this analysis, I will assume that if two players each have a record for a partiucular team for a particular year, they were teammates). Thanks in advance for any help on this query. This self-join may be what you're looking for: SELECT A.Player as Player1, A1.Player AS Player2, A.Team, Count(A.Year) AS [Teammate Years] FROM Table1 A INNER JOIN Table1 A1 ON (A.Team = A1.Team) AND (A.Year = A1.Year) WHERE A.Player < A1.Player GROUP BY A.Player, A1.Player, A.Team; Jul 25 '06 #4

 P: n/a Dan wrote: As I have tested and experimetned withthis query I realized it only works for players who are teamates on one team. It doesn' t pick up guys who may have been teammates on more than one team. For example, Pete Rose and Tony Perez were teammates for 16 years on the Reds but also 1 year on the Phillies. Unfortunately, I am not good enough with sql to make the necessary adjustment ot the query. Any additional thoughts would be apreciated. Dan, I think the query does pick up the same teammates across different teams. Please check your data again. Post your exact table structures, with some sample data, and the SQL of the query you're actually running. Using my original query: Sample Data ----------------------------- Player, Team, Year John Smoltz, Team1, 1990 John Smoltz, Team1, 1991 Pete Rose, Team1, 1990 Pete Rose, Team1, 1991 Pete Rose, Team1, 1992 Pete Rose, Team2, 1993 Tony Perez, Team1, 1990 Tony Perez, Team2, 1993 Query Results ----------------------------- Player1, Player2, Team, Teammate Years John Smoltz, Pete Rose, Team1, 2 John Smoltz, Tony Perez, Team1, 1 Pete Rose, Tony Perez, Team1, 1 Pete Rose, Tony Perez, Team2, 1 --Dan L. DFS wrote: >Dan wrote: >>I am trying to create a query (in either sql or the design view) todetermine which two (or more I suppose if it's not too complicated)baseball players were teammates the longest. The database includesthe following fields: YearId, PlayerId, and teamId.I have been unable to write query that can caluclate the number ofyears players would have played together on a team. (Technical note:some players have records for more than one team for a given yearbecause they may have been traded during the season; for the purposeof this analysis, I will assume that if two players each have arecord for a partiucular team for a particular year, they wereteammates).Thanks in advance for any help on this query. This self-join may be what you're looking for:SELECT A.Player as Player1, A1.Player AS Player2, A.Team,Count(A.Year) AS [Teammate Years]FROM Table1 A INNER JOIN Table1 A1ON (A.Team = A1.Team) AND (A.Year = A1.Year)WHERE A.Player < A1.PlayerGROUP BY A.Player, A1.Player, A.Team; Jul 25 '06 #5

 P: n/a "Dan" Dan wrote: I am trying to create a query (in either sql or the design view) to determine which two (or more I suppose if it's not too complicated) baseball players were teammates the longest. The database includes the following fields: YearId, PlayerId, and teamId. I have been unable to write query that can caluclate the number of years players would have played together on a team. (Technical note: some players have records for more than one team for a given year because they may have been traded during the season; for the purpose of this analysis, I will assume that if two players each have a record for a partiucular team for a particular year, they were teammates). Thanks in advance for any help on this query. This self-join may be what you're looking for:SELECT A.Player as Player1, A1.Player AS Player2, A.Team,Count(A.Year) AS [Teammate Years]FROM Table1 A INNER JOIN Table1 A1ON (A.Team = A1.Team) AND (A.Year = A1.Year)WHERE A.Player < A1.PlayerGROUP BY A.Player, A1.Player, A.Team; -- Bob Quintal PA is y I've altered my email address. -- Posted via a free Usenet account from http://www.teranews.com Jul 25 '06 #6

 P: n/a And, try this one for a by-player-by-year view TRANSFORM First(Team) AS Team1 SELECT Player FROM Table1 GROUP BY Player PIVOT Year; Dan wrote: As I have tested and experimetned withthis query I realized it only works for players who are teamates on one team. It doesn' t pick up guys who may have been teammates on more than one team. For example, Pete Rose and Tony Perez were teammates for 16 years on the Reds but also 1 year on the Phillies. Unfortunately, I am not good enough with sql to make the necessary adjustment ot the query. Any additional thoughts would be apreciated. --Dan L. DFS wrote: >Dan wrote: >>I am trying to create a query (in either sql or the design view) todetermine which two (or more I suppose if it's not too complicated)baseball players were teammates the longest. The database includesthe following fields: YearId, PlayerId, and teamId.I have been unable to write query that can caluclate the number ofyears players would have played together on a team. (Technical note:some players have records for more than one team for a given yearbecause they may have been traded during the season; for the purposeof this analysis, I will assume that if two players each have arecord for a partiucular team for a particular year, they wereteammates).Thanks in advance for any help on this query. This self-join may be what you're looking for:SELECT A.Player as Player1, A1.Player AS Player2, A.Team,Count(A.Year) AS [Teammate Years]FROM Table1 A INNER JOIN Table1 A1ON (A.Team = A1.Team) AND (A.Year = A1.Year)WHERE A.Player < A1.PlayerGROUP BY A.Player, A1.Player, A.Team; Jul 25 '06 #7

 P: n/a Bob Quintal wrote: "Dan" As I have tested and experimetned withthis query I realized itonly works for players who are teamates on one team. Itdoesn' t pick up guys who may have been teammates on more thanone team. For example, Pete Rose and Tony Perez wereteammates for 16 years on the Reds but also 1 year on thePhillies. Unfortunately, I am not good enough with sql tomake the necessary adjustment ot the query. Any additionalthoughts would be apreciated.--Dan L. It works, you'll get two rows, one for Rose+Perez+Reds and one just underneath it for Rose+Perez+Phillies. If you want to sum up the years on different teams, create a new query, based on the one below, that holds the two players names and the years. You won't be able to get it into a single query without a lot of heartache. Select Player1, Player2, sum([Teammate Years]) as [ttlmate years] from query1 group by player1, player2; No need to do that, is there? This one will sum the total years they were teammates on any team: SELECT A.Player AS Player1, A1.Player AS Player2, Count(A.Year) AS [Total Teammate Years] FROM Table1 A INNER JOIN Table1 A1 ON (A.Team = A1.Team) AND (A.Year = A1.Year) WHERE A.Player < A1.Player GROUP BY A.Player, A1.Player; > >DFS wrote: >>Dan wrote:I am trying to create a query (in either sql or the designview) to determine which two (or more I suppose if it's nottoo complicated) baseball players were teammates thelongest. The database includes the following fields:YearId, PlayerId, and teamId.I have been unable to write query that can caluclate thenumber of years players would have played together on ateam. (Technical note: some players have records for morethan one team for a given year because they may have beentraded during the season; for the purpose of this analysis,I will assume that if two players each have a record for apartiucular team for a particular year, they wereteammates).Thanks in advance for any help on this query. This self-join may be what you're looking for:SELECT A.Player as Player1, A1.Player AS Player2, A.Team,Count(A.Year) AS [Teammate Years]FROM Table1 A INNER JOIN Table1 A1ON (A.Team = A1.Team) AND (A.Year = A1.Year)WHERE A.Player < A1.PlayerGROUP BY A.Player, A1.Player, A.Team; -- Bob Quintal PA is y I've altered my email address. Jul 25 '06 #8

### This discussion thread is closed

Replies have been disabled for this discussion.