Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-01-15 : 08:59:45
|
| John writes "I have been given the questions "Which teams have already played each other at both home and away?" and "Give the result of each game(teams and goals)and list the game with the most number of goals at the top". The information I have been given is in the form of tables:Person(personID,name)Player(playerID,DOB,shirt_number,position_code,teamID)Official(OfficialID,normal_otype)Team(teamID,team_name,managerID,coachID)Official_type(otype)Playing_position(position_code,position)Game(gameID,date,game_time,home_teamID,away_teamID)Incident(incidentID,time,playerID,gameID)Goal(incidentID,powerplay,assist_playerID)Penalty(IncidentID,taken_by_playerID,infraction_no,ptype)On_off_ice(incidentID,on_or_off)Penalty_type(ptype,bench_time,removal,time_before_sub, sub_for_goalkeeper)Infraction(infraction_no,infraction_name,goalkeeper_only)Game_player(gameID,playerID)Game_official(gameID,officialID,otype)Infraction_penalty_type(infraction_no,ptype)Do you have any idea what the SQL code for these two questions would be?I would be most grateful." |
|
|
fisherman_jake
Slave to the Almighty Yak
159 Posts |
Posted - 2002-01-16 : 02:37:11
|
Hahahahaha.. Well spotted GRAZ, so I I'll just give pointers to our friend, just in case he has a deadline like tomorrow. John this will not have the SQL code you are after, but it'll point you to the general direction of how to write it..1) Which teams have played each other at BOTH home and away?Tables in use are:Game, Team- the Game table will tell you which home team has played against the away team.- Just make sure that the team has played a home game and an away game against the same away and home team. eg TeamA(home) vs TeamB(away) AND TeamB(home) vs TeamA(away)- the Team table will give you the team names2) Give the result of each game(teams and goals)?Tables in use are:Player, Team, Goal, Game, Game_Player, Incident- for each record in the Game table you can link it through Game_player to get which players played and through that you can get which teams.- Once you know which players played, you can find out through the Goal table which player scored and thus which team gets the score3) List the game with the most number of goals at the top.Tables in use are:Goal, Game, Game_Player, Incident- this one is easier than question 2. Basically you will need to count the number of goals scored per game and ORDER BY the count of scores DESCending.OK.. Write some SQL John and hopefully the guys will take pity on you.. I have to go home now.. ehehehe..    <- I love how this looks.. I think the needs a  .==================================================World War III is imminent, you know what that means... No Bag limits!!!Master Fisherman |
 |
|
|
|
|
|
|
|
Relational database tables