Table JOIN Query Question

[goclimb]

Hi,

I have a question about creating a SQL query that does what I believe to be an inner join between two tables, and spits out some results. I've read a number of items regarding joins, but for some reason I'm not getting the point.

I have the following tables in a database I'm creating to keep track of foosball game scores at work. The only two tables of interest at this particular time are the first two:

+--------------------+
| Tables_in_foosball |
+--------------------+
| game_summary |
| player_list |
| results |
+--------------------+

The data in each table looks like this:

player_list
+-----------+-------------+-------------------+-------------+
| player_id | player_name | player_email | date_joined |
+-----------+-------------+-------------------+-------------+
| 1 | Mark | [e-mail] | 2007-03-17 |
| 2 | Jerry | [e-mail] | 2007-03-17 |
+-----------+-------------+-------------------+-------------+

game_summary
+---------+------------+----------------+---------------+------------+----------------+---------------+-----------
| game_id | player1_id | player1_points | player1_color | player2_id | player2_points | player2_color | game_date
+---------+------------+----------------+---------------+------------+----------------+---------------+-----------
| 1 | 2 | 10 | Yellow | 1 | 6 | Black | 2007-03-19
| 2 | 2 | 10 | Yellow | 1 | 8 | Black | 2007-03-19
| 3 | 2 | 10 | Yellow | 1 | 5 | Black | 2007-03-19
| 4 | 2 | 7 | Yellow | 1 | 10 | Black | 2007-03-19
| 5 | 1 | 2 | Black | 2 | 10 | Yellow | 2007-03-19

All I want to do is this : run a query where I, say, display all the games that Mark (player_id=1) or Jerry (player_id=2) has won. Or, perhaps, I want to sum up all the points from all the games for Mark.

This will all be tied to a web page where the user can select a player name from a drop down menu to execute a particular query against. What I'm not understanding how to do is take the name from the menu, which is tied to a player ID in player_list, and execute a query for that player against game_summary. Or, conversely, list all the scores in game_summary, but instead of printing out the meaningless player_id, print out the actual player_name. That would be the idea query.

Can anybody help?

Thanks,

Mark

[ahundiak]

The fact that you have two sets of players in your summary table complicates things a bit. But for your last question concerning getting player infor from the summary you want something like this:

SELECT 
  game.id AS game_id,
  game.game_date AS game_date,
  player1.id AS player1_id,
  player1.name AS player1_name,
  player2.id AS player2_id,
  player2.name AS player2_name
FROM
  game_summary AS game
LEFT JOIN
  player_list AS player1 ON player1.player_id = game.player1_id,
LEFT JOIN
  player_list AS player2 ON player2.player_id = game.player2_id
WHERE
  game.game_id = 27;

Get the above query to work using a mysql tool and you will probably get a better idea of what joins are all about.

Searching for games by players is complicated by the fact that you have two sets of player ids in your summary table. Considering refactoring into two tables:

game
  game_id
  game_date

game_player
  game_player_id
  game_id
  player_id
  score
  winner  (true/false)

So game player will have two records for each game, one for each player. And once you are comfortable with joins then it's easy to link all the tables together.

[goclimb]

Very cool...thanks! Is there a better way to have constructed this database? I can't think of a better way to create a table that keeps track of scores between two players. But, I would suspect there is.

[kakki]

as ahundiak posted:
split up game_summary into 2 tables