Hi
I'm looking for a DB professional person who can create a VIEW for my DB "MS SQL Server".. below are the details:
The DB is a football games DB, which have tables for Teams, Players, Matches, Matches Player, Matches Event...
The Table TEAMS have all the details about each team.. Name, Cach, Logo..etc
The Table Players have all the details for the Players playing in all the teams in TEAMS table
The Table Online_Data have ONLY ONE RECORD! this record have the values for the active running on the system match {where the operators are logging statistics for..} it have the matchID, Team1 "the TeamID for Home Team", Team2 "the TeamID for Away team", and some other field are for other use.
The Table MatchsPlayer, this table stores records for the line up and substitutes players for each team for each match.. maximum will have 22 records " players "for each team.. the field "PLAYED" in each record indicate if this record is for a substitute player or a line up one {true/false}, where true means the player is playing and false means that the player on the substitutes list
The Table MatchEvents have different many records for all the events happened in the match {Foul, Corner, Shot, Offside, Goal..etc} and every record has the values which indicates this record for which team in which match and done by which player..
Now.. the required VIEW is as follow:
the goal of the required VIEW is to have a FULL details about each player playing for every team {team1 and team2} for the active match in the online_data table.. for example:
Online_Data table have MatchID=3 and Team1=4 and Team2=5
the view should look for this Match players in the MatchPlayer table {playing and substitutes ones}
to show their FULL NAME, TEAM NAME, TOTAL of Fouls, TOTAL of OFFSIDEs, TOTAL of Corners, TOTAL of Shots, Total Of Goals,.. {each player will have a maximum 10 totals of 10 different events in the match}
* the VIWE records should be Ordered by team then by playing status.. for example: it should list the names and totals of the playing players for TEAM1 then the Names and totals for Substitutes for Team1 then the same for TEAM2 Players
attached is the DB tables Script for your reference as well as a sample data for each table.
Good Luck!
I can write the most optimized SQL for you! I have been doing this for all my enterprise applications I have developed during the last 18 years or so. When it comes to writing complex SQL statements, no one can beat me! I do all the complex reports through SQL views. I have been leading teams of software developers for more than 14 years.