Question 88020 in ASP.NET Question posted in
The official documentation can be found here.

In a SELECT command, how do I use data from one table to specify data in another? – Asp.net

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.

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search