CODEWITHSUNDEEP
sqlpostgresql
sebu
sebu

Reputation: 206

SQL: How can I select two IDs as two names from the same table?

I'm new to SQL and trying to get a handle on joins and referencing.

Databases look like this:

Players

 id | name
----+-------
  1 | john
  2 | tim
  3 | frank
  4 | tony
  5 | mark

Matches

 id | winner | loser
----+--------+-------
  1 |      2 |     4
  2 |      3 |     4
  3 |      4 |     5
  4 |      1 |     4

I want to get this result:

 id | winner | loser
----+--------+-------
  1 |    tim |  tony
  2 |  frank |  tony
  3 |   tony |  mark
  4 |   john |  tony

So far I can do this:

select matches.id, players.name as winner, players.name as loser
  from matches, players 
  where winner = players.id;

But if I add and loser = players.id, it returns no rows.

I've also tried this:

select matches.id, players.name as winner, players.name as loser
  from matches
  join players on matches.winner = players.id
  join players on matches.loser = players.id;

which produces ERROR: table name "players" specified more than once.

I have googled this for an hour and can find lots of cases that almost get at what I want, but I still can't figure out how to replace two IDs with two names from the same table.

Upvotes: 2

Views: 3537

Answers (3)

Baltic
Baltic

Reputation: 1

Your will want to make sure that you join twice on that table and keep the alias correct.

SELECT Matches.id, winner.name, loser.name
From Matches
INNER JOIN Players as winner on winner.id = Matches.winner
INNER JOIN Players as loser on loser.id = Matches.winner;

Upvotes: 0

Chris Nava
Chris Nava

Reputation: 6802

You need to alias the table names so you can specify which join the data should come from.

select
    matches.id,
    p1.name as winner,
    p2.name as loser
from matches
  join players p1 on matches.winner = p1.id
  join players p2 on matches.loser = p2.id;

Upvotes: 2

Tim3880
Tim3880

Reputation: 2583

You are almost there:

select matches.id, w.name as winner, l.name as loser
 from matches
 join players w on matches.winner = w.id
 join players l on matches.loser = l.id;

Upvotes: 4

Related Questions