Get the names of fighters from another table via join in Postgres-CodePudding

I have a table called matches that stores information about every match that has happened in the UFC. Inside this table, I have three columns (fighter_a, fighter_b, winner) that are foreign keys, and reference the id from a fighters table. I would like to perform some type of query where I can get the names of the fighters, instead of just their ids. Reason being is I need to display the fighters name on a webpage, just having their id is useless to me.

What would the query look like in order to achieve this? I was thinking we could perform some type of join where each row gets three columns added to it, something like: fighter_a_name, fighter_b_name, and winner_name. I am still relatively new to SQL, so any help would be appreciated.

Here is the matches table

Here is the fighters table

CodePudding user response：

A simple join will do the trick for you. Something like:

SELECT m.*, fa.name AS fighter_a_name, fb.name AS fighter_b_name, fw.name AS winner_name
FROM matches m
         JOIN fighters fa ON fa.id = m.fighter_a
         JOIN fighters fb ON fb.id = m.fighter_b
         JOIN fighters fw ON fw.id = m.winner;

You can join on the same table with different aliases when you have multiple references. Hope it helps.