I nee to edit this VIew and add more information.
I nee to edit this VIew and add more information. I am using MS-SQL ServerI need to add yearID and lgID (from Salaries table), and franchName from Franchises tableFrom the database Files listed below.
USE baseball;IF OBJECT_ID ('player_history','V') IS NOT NULLDROP VIEW player_history;GoCREATE VIEW Player_HistoryASSELECT A.playerID, A.[Player Name], A.[Teams Played], B.[Year Played], C.[TotalSalary], C.[Average Salary], D.[Last College Attended], E.[Last Year Played inthe AllStars], E.teamID AS[ID of Last Team]FROM(select M.playerID, M.nameFirst + ' ' + M.nameLast AS [Player Name], [TeamsPlayed]from Master M left JOIN(select playerID, COUNT(teamID) AS [Teams Played]from FieldingGROUP BY playerID) F ON M.playerID = F.playerID) ALEFT JOIN(select M.playerID, [Year Played]from Master M LEFT JOIN(select PlayerId, count(*) AS [Year Played]from (select PlayerId fromFieldinggroup by PlayerId, YearID) innnerQgroup by innnerQ.PlayerId) F ON M.playerID = F.playerID)BON A.playerID = B.playerIDLEFT JOIN(select playerID, SUM(salary) AS [Total Salary], AVG(salary)Salary]from salariesgroup by playerID) CON A.playerID = C.playerID AS [Average LEFT JOIN(select playerID, MAX(YearID) AS [Year Last Played], MAX(schoolID) AS [LastCollege Attended]FROMCollegePlayingGROUP BY playerID) DON A.playerID = D.playerIDLEFT JOIN(select A.playerID, A.[Last Year Played in the AllStars], MAX(teamID) teamIDfrom(select playerID, MAX(yearID) AS [Last Year Played in the AllStars]FROM AllstarFullGROUP BY playerID) ALEFT JOIN(select playerID, teamID, yearIDfrom AllstarFull) BON A.playerID = B.playerIDAND A.[Last Year Played in the AllStars] = B.YearIDGROUP BY A.playerID, A.[Last Year Played in the AllStars]) EON A.playerID = E.playerID;
Show more
