CODEWITHSUNDEEP
sqljoin
leinho
leinho

Reputation: 59

SQL same join in two columns with different values

I don't know what is the best way to do this

FIGHT

Fighter_ID1 | Fighter_ID2

FIGHTERS

ID | Fighter

OUTPUT

Fighter1 | Fighter2

I want to join the column fighter of the table FIGHTERS with the two columns in table FIGHT..

It can be an stupid question but I don't know how to do this in a correctly way

Sorry about my English and thanks

Upvotes: 2

Views: 2137

Answers (4)

Shawn Lehner
Shawn Lehner

Reputation: 1323

Because you would have 2 fighters for each fight you will want to alias your table. I am typing this directly here so I cannot guarantee it is 100% accurate but it should look something like this:

SELECT * 
FROM FIGHT 
  INNER JOIN FIGHTERS AS F1 ON F1.ID = Fighter_ID1
  INNER JOIN FIGHTERS AS F2 ON F2.ID = Fighter_ID2

Then you can get the fighter names using F1.Fighter and F2.Fighter as your fields.

Upvotes: 1

konstantin
konstantin

Reputation: 725

how about

    select f1.name, f2.name -- pick any column you like
    from FIGHT f -- from here we join both fighters by id
    join FIGHTER f1 on f.fighter_id1 = f1.id
    join FIGHTER f2 on f.fighter_id2 = f2.id

Upvotes: 1

Taryn
Taryn

Reputation: 247680

looks like you need to join on the table twice. 

SELECT fs1.Fighter as Fighter1, fs2.Fighter as Figther2
FROM Fight f
INNER JOIN Fighters fs1
    ON f.Fighter_ID1 = fs1.id
INNER JOIN Fighters fs2
    ON f.Fighter_ID2 = fs2.id

Upvotes: 1

ericb
ericb

Reputation: 3410

SELECT  F1.Fighter as Fighter1,
        F2.Fighter as Fighter2
FROM    FIGHT
        INNER JOIN FIGHTERS as F1 ON FIGHT.Fighter_ID1 = F1.ID
        INNER JOIN FIGTHERS as F2 ON FIGHT.Fighter_ID2 = F2.ID

Upvotes: 2

Related Questions