join two records

i have a database  called  results in sql server 2000

the fields are the following  :
gameid, idteam, points, goalsinfavor, goalsagainst

everytime a manager reports a game played i have set up 2 commands one inserts the record for hometeam and the other inserts the record for the visiting team

so if a game  had the score 4-2   home wins   then it will insert 2 records
and the record for the home is:
gameid= 300
idteam= 4 (unique for each team)
points = 3
goalsinfavor=4
goals against= 2


and the record for the visitor is:
gameid= 300
idteam= 3(unique for each team)
points = 0
goalsinfavor=2
goals against= 4

how can i do to query by gameid=300
so i can show the score for this game?

or any ideas on how to combine those 2 records
LVL 1
gianitooAsked:
Who is Participating?
 
cubical10Connect With a Mentor Commented:
This query will give you the score by game (I assumed that your table is called results).

         SELECT  gameid, MAX(goalsinfavor) AS Winner, MIN(goalsagainst) AS Loser
         FROM results
         GROUP BY gameid;
This is the short fast answer, but no reference to team is here.

You did not provide any detail on the other tables in your db, so I did not have much to work with.  
With some detail of other tables, I could have built a better query...

For a more complete solution all based on this one table (results), you can create two views.
winnersByGameID and losersByGameID.

winnersByGameID is defined as:
    SELECT     gameid, idteam, goalsinfavor, goalsagainst
    FROM         dbo.results
    WHERE     (points = 3);

losersByGameID is defined as:
   SELECT     gameid, idteam, goalsinfavor, goalsagainst
   FROM         dbo.results
   WHERE     (points =0)

then your report query is this:
    SELECT
         winnersbygame.gameid, winnersbygame.idteam AS winningteam, winnersbygame.goalsinfavor AS   WinnerGoals,
         losersbygame.idteam AS losingteam, losersbygame.goalsinfavor AS LoserGoals
    FROM  losersbygame INNER JOIN winnersbygame
    ON losersbygame.gameid = winnersbygame.gameid

N.B: this solution does not allow for tie games!  As I said this is a work around because this is the only table defined.

Hope this helps...
0
 
lschweerConnect With a Mentor Commented:
i assume you need both team ids in one row then use

SELECT t1.gameid, t1.idteam, t2.idteam, t1.goalsinfavor, t1.[goals against]
  FROM Games as t1
  LEFT JOIN Games as t2 ON t1.GameID = t2.GameID and t1.idteam<>t2.idteam
 
there is a drawback for this solution .. you will receive 2 rows per game

if you need one row just filter out the winning teams

  WHERE t1.goalsinfavor >= t1.[goals against]

and use a DISTINCT after the select to remove the duplicates for even games

lars
0
 
lschweerCommented:
as i said this will also work for tie games

SELECT DISTINCT t1.gameid, t1.idteam, t2.idteam, t1.goalsinfavor, t1.[goals against]
  FROM Games as t1
  LEFT JOIN Games as t2 ON t1.GameID = t2.GameID and t1.idteam<>t2.idteam
  WHERE t1.goalsinfavor >= t1.[goals against]

lars
0
 
gianitooAuthor Commented:
with this query  

SELECT distinct t1.gamesid, t1.idofteam, t2.idofteam, t1.goalsinfavor, t1.goalsagainst
FROM dbo.gameresults as t1    LEFT JOIN dbo.gameresults as t2 ON t1.GamesID = t2.GamesID and t1.idofteam<>t2.idofteam
WHERE t1.goalsinfavor >= t1.goalsagainst

i see results like this

gamesid  |   idofteam |   idofteam | goalsinfavor   |  goalsagainst |

230                5                  5                  3                     null


i would like to show different idofteam in the third column and i do not know why the goals against is not showing?

please help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.