In a SELECT command, how do I use data from one table to specify data in another?-CodePudding

I have 2 tables. What is important is the PlayerId and the Username.

CREATE TABLE [dbo].[Run] 
(
    [RunId]        INT       NOT NULL,
    [PlayerId]     INT       NOT NULL,
    [Duration]     TIME(7)   NOT NULL,
    [DateUploaded] NCHAR(10) NOT NULL,
    [VersionId]    INT       NOT NULL,

    PRIMARY KEY CLUSTERED ([RunId] ASC),

    CONSTRAINT [FK_Run_Player] 
        FOREIGN KEY ([PlayerId]) REFERENCES [dbo].[Player] ([PlayerId]),
    CONSTRAINT [FK_Run_Version] 
        FOREIGN KEY ([VersionId]) REFERENCES [dbo].[Version] ([VersionId])
);

CREATE TABLE [dbo].[Player] 
(
    [PlayerId]       INT       NOT NULL,
    [Username]       NCHAR(20) NOT NULL,
    [ProfilePicture] IMAGE     NULL,
    [Country]        NCHAR(20) NOT NULL,
    [LeagueId]       INT       NULL,
    [DateJoined]     DATE      NULL,

    PRIMARY KEY CLUSTERED ([PlayerId] ASC),

    CONSTRAINT [FK_Player_League] 
        FOREIGN KEY ([LeagueId]) REFERENCES [dbo].[League] ([LeagueId])
);

I have a select command:

SELECT 
    PlayerId, Duration, VersionId, DateUploaded 
FROM 
    [Run]

(with apologies in advance for my messy made up pseudocode), what I need it to do is:

SELECT (Player.PlayerId.Username)

What I basically need it to do, is instead of giving me just PlayerId, I need it to get the corresponding Username (from the other table) that matches each PlayerId (PlayerId is a foreign key)

So say for example instead of returning

1, 2, 3, 4, 5

it should return

John12, Abby2003, amy_932, asha7494, luke_ww

assuming, for example, Abby2003's PlayerId was 2.

I've done trial and error and either nobody's tried this before or I'm searching the wrong keywords. This is using VS 2022, ASP.NET Web Forms, and Visual Basic, but that shouldn't affect anything I don't think. Any syntax ideas or help would be greatly appreciated.

CodePudding user response：

Usually in this case joins are used. You can join the two tables together, give them aliases (or don't, personal preference really), then select what you need. In this case, you would probably want an inner join. Your query would probably look something like this:

SELECT p.Username FROM [Run] r
INNER JOIN [Player] p ON r.PlayerId = p.PlayerId

Then if you need to you can put a WHERE clause after that.

More about joins here

CodePudding user response：

try this for join the 2 Table togother

SELECT R.RunId
  ,R.PlayerId
  ,R.Duration
  ,R.DateUploaded
  ,R.VersionId
  ,P.Username
  ,P.ProfilePicture
  ,P.Country
  ,P.LeagueId
  ,P.DateJoined
   FROM Run R
   inner join Player P on R.PlayerId = P.PlayerId