Complicated SQL Query involving multiple tables

I think the easiest approach is to concatenate the teams together and just join on the results. Postgres provides the function string_agg() to aggregate strings:

select p1.playerId, p1.playerName, p2.playerId, p2.playerName
from (select p.playerId, string_agg(cast(p.TeamId as varchar(255)), ',' order by TeamId) as teams,
             pp.PlayerName
      from plays p join
           players pp
           on p.playerId = pp.playerId
      group by p.playerId
     ) p1 join
     (select p.playerId, string_agg(cast(p.TeamId as varchar(255)), ',' order by TeamId) as teams,
             pp.PlayerName
      from plays p join
           players pp
           on p.playerId = pp.playerId
      group by p.playerId
     ) p2
     on p1.playerid < p2.playerid and p1.teams = p2.teams;

EDIT:

You can do this without string_agg. The idea is to start with a list of all possible player combinations.

Then, join in the teams for the first player using left outer join. And join in the teams for the second by using full outer join and matching on the team and driver name. The reason you need the driver table is to be sure that the id/name does not get lost in the full outer join:

select driver.playerid1, driver.playerid2
from (select p1.playerId as playerId1, p1.playerName as playerName1,
             p2.playerId as playerId2, p1.playerName as playerName2
      from players p1 cross join
           players p2
      where p1.playerId < p2.playerId
     ) driver left outer join
     plays p1
     on p1.playerId = driver.playerId full outer join
     plays p2
     on p2.playerId = driver.playerId and
        p2.teamid = p1.teamid
group by driver.playerid1, driver.playerid2
having count(p1.playerid) = count(*) and
       count(p2.playerid) = count(*);

This joins two players on the team id (with ordering so a pair only gets considered once). It then says there is a match when all the rows for the two players have non-NULL team values. This is perhaps more clear with the equivalent having clause:

having sum(case when p1.playerid is null then 1 else 0 end) = 0 and
       sum(case when p2.playerid is null then 1 else 0 end) = 0;

The full outer join will produce NULL values when two players have teams that don't match. So, no NULL values mean that all the teams match.

This is an adaptation of my answer to a previous question of yours.

1. Get all unique combinations of players using a triangular join:

   SELECT p1.playerID, p1.playerName, p2.playerID, p2.playerName
   FROM player p1
   INNER JOIN player p2 ON p1.playerID < p2.playerID
   

2. Subtract the second player's team set from that of the first player and check if there are no rows in the result:

   NOT EXISTS (
       SELECT teamID
       FROM plays
       WHERE playerID = p1.playerID
   
       EXCEPT
   
       SELECT teamID
       FROM plays
       WHERE playerID = p2.playerID
   )
   

3. Swap the sets, subtract and check again:

   NOT EXISTS (
       SELECT teamID
       FROM plays
       WHERE playerID = p2.playerID
   
       EXCEPT
   
       SELECT teamID
       FROM plays
       WHERE playerID = p1.playerID
   )
   

4. Finally, apply both conditions to the result of the triangular join in Step 1.

   SELECT p1.playerID, p1.playerName, p2.playerID, p2.playerName
   FROM player p1
   INNER JOIN player p2 ON p1.playerID < p2.playerID
   WHERE
       NOT EXISTS (
           SELECT teamID
           FROM plays
           WHERE playerID = p1.playerID
   
           EXCEPT
   
           SELECT teamID
           FROM plays
           WHERE playerID = p2.playerID
       )
   AND
       NOT EXISTS (
           SELECT teamID
           FROM plays
           WHERE playerID = p2.playerID
   
           EXCEPT
   
           SELECT teamID
           FROM plays
           WHERE playerID = p1.playerID
       )
   ;