Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.

like image 720
thenextmogul Avatar asked Nov 25 '25 16:11

thenextmogul


2 Answers

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.

like image 100
citsonga Avatar answered Nov 28 '25 06:11

citsonga


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

like image 36
Daniel E. Avatar answered Nov 28 '25 04:11

Daniel E.



Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!