Tough Select Query

[Dan]

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.

[DFS]

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;

[Dan]

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;

[Dan]

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;

[DFS]

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) 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;

[Bob Quintal]

"Dan" <sp******@hotma il.comwrote in
news:11******** **************@ m79g2000cwm.goo glegroups.com:

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.

It works, you'll get two rows, one for Rose+Perez+Reds and one
just underneath it for Rose+Perez+Phil lies.

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;

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;

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[DFS]

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) 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;

[DFS]

Bob Quintal wrote:

"Dan" <sp******@hotma il.comwrote in
news:11******** **************@ m79g2000cwm.goo glegroups.com:

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

It works, you'll get two rows, one for Rose+Perez+Reds and one
just underneath it for Rose+Perez+Phil lies.

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 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
partiucula r 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.Yea r) 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;

--
Bob Quintal

PA is y I've altered my email address.