Can someone help?

zeus

I have one database with three tables in it. I want to query rows from all 3 tables. All 3 tables are identical in structure and have the exact same columns. Fields- Name(varchar), Team(varchar), Year(int), Games Played(int), Touchdowns(int), Points(int).

It's for an amateur sports website. Each table is for a year- 2004, 2003 and 2002. I want to be able to display statistics for an individual player who has played in all 3 years.

What is the best way to do this? Please explain in terms a newcomer can understand.

If anybody has any suggestions or if I can be pointed in the direction of a tutorial or some documentation on what I am trying to accomplish here it would be greatly appreciated.

Thanks in advance.

Sxooter

There are two basic methods for combining information from more than one table in SQL. One way is to join them (think of this as a horizontal connection) and the other is to union them (this is more like "stacking" the two sets one atop the other.)

You will likely be "stacking" your years together here, so a union. Something like this:

select from year2002
where athelete='Barry Bonds'
UNION
select from year2003
where athelete='Barry Bonds'
UNION
select * from year2004
where athelete='Barry Bonds'

Assuming your database knows what a union is. Older versions of MySQL don't, and you have to use a temp table and select each set of data into that temp table then select it back out of there.

godwheel

I would recommend having multiple tables. Think about what you want to do overall.

If there are more than 1 team, have a table just to list the teams.

A team can have many players, but a player can only be part of ONE team... OR does this take into consideration trades etc...

Think the logic through and it might be easier to write queries to get the info you want (will also make your life easier in the future).

TABLE: Teams
PK TeamID
TeamName

Example: Tigers, Bears, Birds

TABLE: Players
PK PlayerID
Name
PF TeamID
Year
GamesPlayed
Touchdowns
Points

Example: John Michael, Misco Jones, Miguel Cabreras

TABLE: Years
Year

Example: 2003,2002,2001

I know I'm missing something, so please, someone else chime in.

Sxooter

godwheel is spot on. Instead of a different table for each year, I'd look at having year just be a field in the master table.

Then you can just search on a range of years.