Sagit Khaliullin Sagit Khaliullin - 1 month ago 7
SQL Question

How to combine two rows in one in PostgreSQL?

I'm selecting data from 2 tables in postgres in this way:

SELECT matches.id id, first_team_id T1, second_team_id T2, name
FROM matches
JOIN teams ON matches.first_team_id = teams.id
UNION
SELECT matches.id id, first_team_id T1, second_team_id T2, name
FROM matches
JOIN teams ON matches.second_team_id = teams.id


That's how my table looks now:



id T1 T2 name
1 1 2 Team1
1 1 2 Team2
2 1 3 Team2
2 1 3 Team1


That's what i need



id T1 T2 name1 name2
1 1 2 Team1 Team2
2 1 3 Team2 Team1


I need the easiest way to do it. I've seen some solutions in similar questions, but i didn't manage with them. Please help me

Answer

You can do what you want with two joins:

select m.id, m.first_team_id, m.second_team_id, t1.name, t2.name
from matches m join
     teams t1
     on m.first_team_id = t1.id join
     teams t2
     on m.second_team_id = t2.id;