CODEWITHSUNDEEP
sqlpostgresqlselectrelational-database
Manuel Del Toro
Manuel Del Toro

Reputation: 17

get the name of the of each field on a table sql

I am trying to do a simple query but I can not get the desired results.

I have a table BATTLE with two references id's. what I want is to get in one row, the name of each hero based on other table HERO

BATTLE

HERO

I am doing this query

SELECT 
  battle.character1_id as p1,
  character.name,
  battle.character2_id as p2,
  character.name
FROM 
 battle, 
 character 
WHERE 
 character.id in (character1_id, character2_id)

But I am getting two rows by battle. I know I am doing something wrong but I don't know what?

result

Upvotes: 1

Views: 35

Answers (1)

Mureinik
Mureinik

Reputation: 312259

You need to join battle on the hero table twice, once for each here:

SELECT h1.id AS id1, h1.name AS name1, h2.id AS id2, h2.name AS name2
FROM   battle b
JOIN   hero h1 ON h1.id = b.hero1_id
JOIN   hero h2 ON h2.id = b.hero2_id

Upvotes: 2

Related Questions