CODEWITHSUNDEEP
sqlsql-serverdatabase
thenextmogul
thenextmogul

Reputation: 1183

SQL Server : Query Joining Two Tables

Trying to create a SQL Server query based on a challenge match system. There are two tables that I want to join in the query: Users and ChallengeMatches.

The columns in the Users table are UserID, UserDisplayName

The columns in the ChallengeMatch table are ChallengeMatchID, ChallengerUserID, ChallengeeUserID

Of course, all ChallengeeUserID's and ChallengerUserID's are listed in the Users table as unique users. What I want the query to do is for each challenge match (row in the ChallengeMatch table), return the UserDisplayName of both of the challenge participants e.g. PlayerName1, PlayerName2.

Any help would be appreciated.

Upvotes: 2

Views: 111

Answers (2)

Daniel E.
Daniel E.

Reputation: 2059

Select * FROM 
    (
      SELECT 'Challenger' [Type]
          ,ChallengerUserID [UserID] 
      from ChallengeMatch 
      where ChallengeMatchID=2
     UNION ALL
      SELECT 'Challengee' [Type]
           ,ChallengeeUserID [UserID] 
      from ChallengeMatch 
      where ChallengeMatchID=2
          ) as c
Inner join Users u
  ON c.UserID=u.UserID

Returns results in slightly different form than @Citsonga, but works too.
I would consider storing information more like how the nested select looks in general (Except Type would be an Int foreign Key to the user types table that you would need) for easier joins of this nature. It would also allow you to expand to more than 1 challenger/e per match

Upvotes: 2

citsonga
citsonga

Reputation: 323

You need to join the Users table twice. This should do the trick:

select 
    u1.UserDisplayName as [Challenger],
    u2.UserDisplayName as [Challengee]
from ChallengeMatch cm
left outer join Users u1
    on cm.ChallengerUserID = u1.UserID
left outer join Users u2
    on cm.ChallengeeUserID = u2.UserID
where cm.ChallengeMatchID = 2 /* specify value for relevant match */

Here is a SQL Fiddle with this example working.

Upvotes: 7

Related Questions