Posgresql: How to get values that have been replaced by keys id in another table?

Question

I have 2 tables with database like below. The column name in table A are replaced by id of table B

Match
id  my_member_id    your_member_id  date    score
1       2                   4       ...     ...
2       3                   4       ...     ...
3       1                   3       ...     ...
4       2                   3       ...     ...

Team
id          member_name
1               A
2               B
3               C
4               D

I want result like below:

Table Versus
date    my_member   your_member score
...         B           D       ...
...         C           D       ...
...         A           C       ...
...         B           C       ...

My query is taking forever to execute so I think it's wrong in somewhere

WITH RECURSIVE my_team AS
(
    SELECT m.id, t.member_name AS my_member
    FROM Team t INNER JOIN Match m ON t.id = m.id
),
your_team AS
(
    SELECT id, my_member, ''::TEXT AS your_member
    FROM my_team 
    
    UNION ALL
    
    SELECT mt.id, mt.my_member, cte.your_member
    FROM my_team mt INNER JOIN your_team cte ON mt.id = cte.id
)
SELECT date, my_member, your_member, score
FROM Match m INNER JOIN your_team yt at ON m.id = yt.id

Answer 1

You do not need recursion for this.

Join into team twice, using aliases:

select m.date, my.member_name as my_member, y.member_name as your_member, m.score
  from match m
  join team my on my.id = m.my_member_id
  join team y on y.id = m.your_member_id;