Help joining these two tables

[msaz87]

Hey all,

 

I was hoping to get some help joining these two tables...

 

The first table is called "standing"

 

And this one is "game"

 

I didn't set these tables up, so they're not designed to make my life easier, but I'll try to explain what's going on:

 

Each game (game_id) has team_1 and team_2 and their respective scores are kept in the standing table, along with whether they won or lost (loose wasn't my typo...) and what I'm looking to do with joining them is to be able to output all the games in a week WHERE the location_id and league_id match my criteria, but the games would output with team_1, team_2 and their pts_scored.

 

Using the two shots above, here's an example:

 

TEAM 1.....PTS SCORED

50............54

46............7

 

51............37

45............15

 

Any suggestions and help is greatly appreciated.. thanks!

[kickstart]

Hi

 

Nearest I can see would be this

 

SELECT game.game_id, team_1, pts_scored, '1' AS teamOrder
FROM game
INNER JOIN standing
ON game.game_id = standing.game_id
AND game.team_1 = standing.team_id
WHERE location_id = '$location'
AND league_id = '$league'
UNION
SELECT game.game_id, team_2, pts_scored, '2' AS teamOrder
FROM game
INNER JOIN standing
ON game.game_id = standing.game_id
AND game.team_2 = standing.team_id
WHERE location_id = '$location'
AND league_id = '$league'
ORDER BY game_id, teamOrder

 

This is simpler but gives you both scores for a game on 1 row.

 

SELECT game.game_id, team_1, standing1.pts_scored, team_2, standing2.pts_scored
FROM game
INNER JOIN standing1
ON game.game_id = standing1.game_id
AND game.team_1 = standing1.team_id
INNER JOIN standing2
ON game.game_id = standing2.game_id
AND game.team_2 = standing2.team_id
WHERE location_id = '$location'
AND league_id = '$league'

 

All the best

 

Keith