Help with query

[SurfScape]

I have 3 tables (actually alot more, but three for this query):

#
# Table structure for table `game_mastertbl`
#

CREATE TABLE game_mastertbl (
id int(11) NOT NULL auto_increment,
game_id varchar(20) NOT NULL default '',
tourney_id int(11) NOT NULL default '0',
division_id int(11) NOT NULL default '0',
home_team_id int(11) NOT NULL default '0',
home_points int(11) default NULL,
away_team_id int(11) NOT NULL default '0',
away_points int(11) default NULL,
field_id int(11) NOT NULL default '0',
time time NOT NULL default '00:00:00',
PRIMARY KEY (id)
) TYPE=MyISAM;
# --------------------------------------------------------

#
# Table structure for table `points_mastertbl`
#

CREATE TABLE points_mastertbl (
game_id int(11) NOT NULL default '0',
team_id int(11) NOT NULL default '0',
points int(11) default NULL,
) TYPE=MyISAM;
# --------------------------------------------------------

#
# Table structure for table `team_mastertbl`
#

CREATE TABLE team_mastertbl (
id int(11) NOT NULL auto_increment,
tourney_id int(11) NOT NULL default '0',
division_id int(11) NOT NULL default '0',
team_name varchar(255) NOT NULL default '',
PRIMARY KEY (id)
) TYPE=MyISAM;

#---------------------------------------------------------

I have the following query which gives me the game information I am looking for:

SELECT g.id,
t1.team_name as home,
t2.team_name as away,
g.time,
f.field_name
FROM game_mastertbl AS g,
team_mastertbl as t1,
team_mastertbl as t2,
field_mastertbl as f
WHERE g.division_id = 1
AND g.tourney_id = 1
AND g.home_team_id = t1.id
AND g.away_team_id = t2.id
AND g.field_id = f.id
ORDER BY 'time' ASC

Which produces the following results:
id home away time field_name
1 Team A Team B 10:10:00 Cactus Park #3
2 Team C Team D 10:10:00 Cactus Park #4
and so on....

I would like to revise this query so that it includes the scores from the points_mastertbl and I can't seem to get it to work. The desired results would be something like:

id home away hm_pts aw_pts time field_name
1 Team A Team B 7 2 10:10:00 Cactus Park #3
2 Team C Team D 0 4 10:10:00 Cactus Park #4
and so on...

I tried this following query, but it only gave me 3 records in the result, I've tried everything I can think of with no luck:

SELECT g.id,
t1.team_name as home,
t2.team_name as away,
p1.points as home_pts,
p2.points as away_points,
g.time,
f.field_name
FROM game_mastertbl AS g,
team_mastertbl as t1,
team_mastertbl as t2,
field_mastertbl as f,
points_mastertbl as p1,
points_mastertbl as p2
WHERE g.division_id = 1
AND g.tourney_id = 1
AND g.home_team_id = t1.id
AND g.away_team_id = t2.id
AND g.field_id = f.id
AND g.id = p1.game_id
AND g.id = p2.game_id
AND t1.id = p1.team_id
AND t2.id = p2.team_id
ORDER BY `time` ASC

Any help is appreciated.

 

[SurfScape]

Actually in the game_mastertbl the home_points and away_points columns were something I added in for testing, with these columns I can get the desired query result, but had difficulty adding (SUM) the points for the teams and displaying standings, this is why I went to a points table. It made the standing query much easier, but now I can't get the individual game points to display properly.

My table structures are not in stone, so any suggestions are appreciated.

 

[hos2]

when queries are going to be complicated I make it easier by making certain updates all the time. in this case you could run an update querie to sum all the points a team has to the gametable. (you can add those update queries in your scripts. make sure that the values doesn't add up)

 

[SurfScape]

so what are you saying have points in both places game and point table, one constantly updating the other, get game info from game tbl and standings from point table?

 

[SurfScape]

Never mind I figured it out, left the points in the game table.