Combine Data from Two Tables-Help Needed

Question

I have developed a web site with a mysql backend, but am not satisfied with how I am getting one set of data and do not know how to get another dataset.

The page in question is at:

http://whistclub.org/test/ajax.php?vichill/results/1

The results are shown through October so there is some data to use.

I am pulling the results from two mysql tables (see below), but the code I have used is too ugly for me to tolerate. Yeah, it works, but I have some standards! Think calls to the database for each team. I think joins should be used, but I can't get it to work.
Ideally (I think) the result array would convert the teamIDs to team names and use names for the winner and loser VP columns.

The standings table on the page is not working at all--what you see is hand coded just to show what is needed.

The tables are:

teams:

id--autoincremented
teamID--Asssigned to each team as part of the game
teamName

games:

id--autoincremented
gameID--I suspect this is not needed since it duplicates id.
teamA
teamB 
date
winner
IMPmargin
winnersVPs
losersVPS

The game is bridge and IMP and VP are scores. VPs are derived from IMPs and the loser often gets a few VPs. See the webpage for the details, but I do not think that is relevant to my issue.

Here is some data from the games table:

id  gameID  teamA  teamB  date        winner  IMPmargin  winnersVPs  losersVPs
1   1       11     18     2013-09-25  18      12         20          10
2   2       12     17     2013-09-25  12      22         22          8
3   3       13     16     2013-09-25  13      20         21          9
4   4       14     15     2013-09-25  14      0          15          15
5   5       19     99     2013-09-25  NULL    NULL       NULL        NULL

Team 99 is a dummy for a bye week for a team.

If the tables are designed too badly, let me know how to make them work better.

Hopefully, that is enough for someone to point me in the right direction.

Answer 1

A join is indeed what you are after.

Consider the following sqlfiddle - http://sqlfiddle.com/#!2/f5412/1/0 (all parts are found below, that just makes it easier if you want to... well, fiddle with it)

The DDL is:

create table team (
    id int,
    teamId int,
    teamName varchar(16)
  );

create table game (
    id int,
    gameId int,
    teamA int,
    teamB int,
    gameDate date,
    winner int,
    IMPmargin int
  );

create table vp (
    margin int,
    winVp int,
    loseVp int
  );

The associated data:

insert into team values
  (1,1,"Team A"),
  (2,2,"Team B"),
  (3,3,"Team C");

insert into game values
  (1,1,1,2,'2013-09-25',1,19),
  (2,2,1,3,'2013-09-25',1,10),
  (2,2,2,3,'2013-09-25',2,14);

insert into vp values
  (10,0,0),
  (11,2,1),
  (12,4,3),
  (13,7,5),
  (14,10,8),
  (15,13,11),
  (16,17,14),
  (18,26,22),
  (19,32,27);

And the query:

select
  G.id,
  A.teamName as "team A",
  B.teamName as "team B",
  G.gameDate,
  W.teamName as "winner",
  G.IMPmargin,
  V.winVp,
  V.loseVp
from
  game G
  join team A on (G.teamA = A.id)
  join team B on (G.teamB = B.id)
  join team W on (G.winner = W.id)
  join vp V on (G.IMPmargin = V.margin)

Note how the relationship between the game (G) is made to the team table three times - as A, B, and W.

You can see the relationships between these in the diagram:

 +---------------+       +----------------+     +--------------+
 | team          |       | game           |     | vp           |
 |---------------|       |----------------|     |--------------|
 | id            +---+   | id             |  +--+ margin       |
 | teamId        |   |   | gameId         |  |  | winVp        |
 | teamName      |   +---+ teamA          |  |  | loseVp       |
 +---------------+   +---+ teamB          |  |  +--------------+
                     |   | gameDate       |  |
                     +---+ winner         |  |
                         | IMPmargin      +--+
                         +----------------+

If you have a discrete victory point table, you can insert that into the database too and join against the margin to the victory points.

---

Back to the joins themselves...

When you do a Join team A on (G.teamA = A.id) you are saying that a this thing in this table is a that thing in another table. That the teamA value in game is the same as the id in the team table.

Once you have that relationship established (and again for B, and W), you can select things from the tables A, B, and W as part of the query.

---

You may also find the following to be helpful:

• Coding Horror - A Visual Explanation of SQL Joins

And some books (O'Reilly is a publisher I trust and while I don't have these books myself, I'm fairly confident you will find them useful references)

• Amazon - Learning MySQL
• Amazon - MySQL Cookbook
• Amazon - Learning SQL