Creating a view in Sql with connected tables

debugcn 投稿 Dev

Jack Henry

I am trying to create a view off of two different tables. Basically I have 2 tables

(MatchStats- Holds all of the fixture information)

(Team - Holds all of the team information)

Here is the code for both tables

Team

    CREATE TABLE TEAM
(
TeamID int NOT NULL,
TeamName varchar(30) NOT NULL,
Teamlocale varchar(30) NOT NULL,

CONSTRAINT pkTeamID PRIMARY KEY (TeamID),

)

MatchStats

CREATE TABLE MATCHSTATS
(
MatchStatsID int NOT NULL,
Team1ID int NOT NULL,
Team1Goals int NOT NULL,
Team2ID int NOT NULL,
Team2Goals int NOT NULL,
RefereeID int NOT NULL,
PitchID int NOT NULL,
sessionTime varchar(20) NOT NULL,
sessionDate varchar(20) NOT NULL,

CONSTRAINT pkMatchStatsID PRIMARY KEY (MatchStatsID),
CONSTRAINT fkTeam1ID FOREIGN KEY (Team1ID) REFERENCES TEAM(TeamID),
CONSTRAINT fkTeam2ID FOREIGN KEY (Team2ID) REFERENCES TEAM(TeamID),
CONSTRAINT fkReferee FOREIGN KEY (RefereeID) REFERENCES REFEREE(RefereeID),
CONSTRAINT fkpitchID FOREIGN KEY (PitchID) REFERENCES Pitch(pitchID),
)

I am trying to create a view that will display the fixture information but use the team name instead of ID. I have been researching and looking at previous questions online and I just cant seem to understand how to do it

This is what I have managed to come up with

    CREATE VIEW FIXTUREHSITORY AS
SELECT m.Team1ID, m.Team1Goals, m.Team2ID, m.Team2Goals, T.TeamName
from MATCHSTATS as m 
JOIN TEAM as t ON t.TeamID on

Overall I am trying to create a report that looks like this

Team 1 Name | Goals | Team 2 Name | Goals

Sorry if I seem vague and thanks in advance

Nishant Gupta

A solution to your problem:

MSSQL

CREATE VIEW FIXTUREHISTORY AS
SELECT t1.TeamName Team1Name, 
       m.Team1Goals Team1Goals, 
       t2.TeamName Team2Name, 
       m.Team2Goals Team2Goals
from MATCHSTATS as m 
INNER JOIN TEAM as t1 ON t1.TeamID = m.Team1ID
INNER JOIN TEAM as t2 ON t2.TeamID = m.Team1ID

Demo Link: