LEFT JOIN with only one match from ordered list

Question

I have the following Team_members table:

+-------+-------+--------+
| ID_no | Name  |  Team  |
+-------+-------+--------+
|     1 | John  | Green  |
|     2 | Carry | Green  |
|     3 | Susan | Yellow |
|     4 | Lola  | Green  |
|     5 | Marc  | Yellow |
|     6 | Mike  | Yellow |
|     7 | Luke  | Yellow |
+-------+-------+--------+

and the following Free_positions table

+----+--------+-------------+
| RN |  Team  | ID_position |
+----+--------+-------------+
|  1 | Green  | A1          |
|  2 | Green  | A2          |
|  3 | Green  | A3          |
|  4 | Green  | A4          |
|  5 | Green  | A5          |
|  6 | Green  | A6          |
|  7 | Green  | A7          |
|  8 | Green  | A8          |
|  1 | Yellow | A21         |
|  2 | Yellow | A22         |
|  3 | Yellow | A23         |
|  4 | Yellow | A24         |
|  5 | Yellow | A25         |
|  6 | Yellow | A26         |
|  7 | Yellow | A27         |
+----+--------+-------------+

My goal is to match every member of the first table with one of the free positions reserved for each team. The query should follow the order of RN in the second table, so the result should be the following:

+-------+-------+--------+-------------+
| ID_no | Name  |  Team  | ID_position |
+-------+-------+--------+-------------+
|     1 | John  | Green  | A1          |
|     2 | Carry | Green  | A2          |
|     3 | Susan | Yellow | A21         |
|     4 | Lola  | Green  | A3          |
|     5 | Marc  | Yellow | A22         |
|     6 | Mike  | Yellow | A23         |
|     7 | Luke  | Yellow | A24         |
+-------+-------+--------+-------------+

I have tried with several left join but I can't figure out how to match only once of each row of the right table. The second problem was how to follow the order of RN column for each Team.

This is what I have tried

/* FIRST TABLE: #Team_members */
SELECT *
INTO #Team_members
FROM (
    SELECT '1' AS [ID_no],'John' AS [Name],'Green' AS [Team] UNION ALL
    SELECT '2' AS [ID_no],'Carry' AS [Name],'Green' AS [Team] UNION ALL
    SELECT '3' AS [ID_no],'Susan' AS [Name],'Yellow' AS [Team] UNION ALL
    SELECT '4' AS [ID_no],'Lola' AS [Name],'Green' AS [Team] UNION ALL
    SELECT '5' AS [ID_no],'Marc' AS [Name],'Yellow' AS [Team] UNION ALL
    SELECT '6' AS [ID_no],'Mike' AS [Name],'Yellow' AS [Team] UNION ALL
    SELECT '7' AS [ID_no],'Luke' AS [Name],'Yellow' AS [Team]
) T

/* SECOND TABLE: #Free_positions */
SELECT *
INTO #Free_positions
FROM (
    SELECT '1' AS [RN],'Green' AS [Team],'A1' AS [ID_position] UNION ALL
    SELECT '2' AS [RN],'Green' AS [Team],'A2' AS [ID_position] UNION ALL
    SELECT '3' AS [RN],'Green' AS [Team],'A3' AS [ID_position] UNION ALL
    SELECT '4' AS [RN],'Green' AS [Team],'A4' AS [ID_position] UNION ALL
    SELECT '5' AS [RN],'Green' AS [Team],'A5' AS [ID_position] UNION ALL
    SELECT '6' AS [RN],'Green' AS [Team],'A6' AS [ID_position] UNION ALL
    SELECT '7' AS [RN],'Green' AS [Team],'A7' AS [ID_position] UNION ALL
    SELECT '8' AS [RN],'Green' AS [Team],'A8' AS [ID_position] UNION ALL
    SELECT '1' AS [RN],'Yellow' AS [Team],'A21' AS [ID_position] UNION ALL
    SELECT '2' AS [RN],'Yellow' AS [Team],'A22' AS [ID_position] UNION ALL
    SELECT '3' AS [RN],'Yellow' AS [Team],'A23' AS [ID_position] UNION ALL
    SELECT '4' AS [RN],'Yellow' AS [Team],'A24' AS [ID_position] UNION ALL
    SELECT '5' AS [RN],'Yellow' AS [Team],'A25' AS [ID_position] UNION ALL
    SELECT '6' AS [RN],'Yellow' AS [Team],'A26' AS [ID_position] UNION ALL
    SELECT '7' AS [RN],'Yellow' AS [Team],'A27' AS [ID_position]
) T

/* JOIN */
SELECT A.*, B.ID_position
FROM #Team_members A LEFT JOIN #Free_positions B
ON A.[Team] = B.[Team] AND B.RN = 1

Answer 1

You can try this:

SELECT A.ID_No, A.NAME, A.TEAM, B.ID_POSITION
FROM (SELECT ID_No, NAME, TEAM, ROW_NUMBER() OVER (PARTITION BY TEAM ORDER BY ID_NO) AS RN_TM
FROM TEAM_MEMBERS) A
LEFT JOIN FREE_POSITIONS B ON  A.RN_TM=B.RN AND A.TEAM=B.TEAM;