CODEWITHSUNDEEP
sql
HellOfACode
HellOfACode

Reputation: 1743

SQL query for data extraction from two tables

I that bean a long time since i have done anything with a sql database regarding queries. I have two tables:

How can i write a select query to get a output such as team_name, 1st_player_name, 2nd_player_name?

I'm repeating the basics but this could give me a jump start.

Upvotes: 1

Views: 2352

Answers (1)

marc_s
marc_s

Reputation: 755531

How about

SELECT
    t.team_Name,
    p1.name + ' ' + p1.surname AS '1st_player_name',
    p2.name + ' ' + p2.surname AS '2nd_player_name'
FROM
    team t
INNER JOIN 
    player p1 ON t.player1_id = p1.id
INNER JOIN 
    player p2 ON t.player2_id = p2.id

You basically need to join the team to the player table twice - once for each of the two players. Since I assume a team always must have both a player #1 and #2, you can use an INNER JOIN for each - this means it will match the team and player tables on equality (of the team.player1_id = player.id and team.player2_id = player.id) between those tables.

Upvotes: 1

Related Questions