CODEWITHSUNDEEP
sqlsql-serverview
user3120540
user3120540

Reputation: 27

SQL Server views - select column via ID

i'm having problems trying to select columns when generating views.

The view would be generated using the two following tables:

Teams Table:

╔════════╦══════════════╦══╗
║ teamID ║   teamName   ║  ║
╠════════╬══════════════╬══╣
║      1 ║ exampleTeam  ║  ║
║      2 ║ exampleTeam2 ║  ║
╚════════╩══════════════╩══╝

Fixtures Table (homeTeamID and awayTeamID are both linked to teamID in Teams table):

    ╔═══════════╦════════════╦════════════╗
    ║ fixtureID ║ homeTeamID ║ awayTeamID ║
    ╠═══════════╬════════════╬════════════╣
    ║         1 ║          1 ║          2 ║
    ╚═══════════╩════════════╩════════════╝

When generating the view, I would like to select and display the teamName field rather than teamID for both teams playing in a fixture. The intended view results would be displayed as follows:

╔═══════════╦═════════════╦══════════════╗
║ fixtureID ║  homeTeam   ║   awayTeam   ║
╠═══════════╬═════════════╬══════════════╣
║         1 ║ exampleTeam ║ exampleTeam2 ║
╚═══════════╩═════════════╩══════════════╝

I would be greatful for any tips as to how I could go about achieving this. I apologize in advance for the terrible formatting.

Upvotes: 0

Views: 68

Answers (1)

Felix Cen
Felix Cen

Reputation: 763

You can do it this way in the sql query

SELECT F.fixtureID, 
       HomeTeam.teamName as homeTeam,
       AwayTeam.teamName as awayTeam   
FROM Fixtures F
INNER JOIN Teams HomeTeam
ON F.homeTeamId = HomeTeam.teamID 
INNER JOIN Teams AwayTeam
ON F.awayTeamId = AwayTeam.teamID

The create view query will look like

CREATE VIEW FixtureView
AS

SELECT F.fixtureID, 
           HomeTeam.teamName as homeTeam,
           AwayTeam.teamName as awayTeam   
    FROM Fixtures F
    INNER JOIN Teams HomeTeam
    ON F.homeTeamId = HomeTeam.teamID 
    INNER JOIN Teams AwayTeam
    ON F.awayTeamId = AwayTeam.teamID

Upvotes: 1

Related Questions