Output sql query to new sql style "for each"

I am trying to execute a nested query and I am having problems. I have two tables (simplified for this question) with player information and another with team information. My ultimate goal is to find players who are on more than one team and then print out which team they are on.

I created a sqlfiddle for testing using MS SQL Server 2014. Here is a small pictorial representation of my problem (note that I missed the last command from this test suite in my database initialization code):

[EDIT:] The sqlfiddle site is having some problems, so I added the DB init code [EDIT2:] sqlfiddle again

 CREATE TABLE Players (
  userid int NOT NULL,
  name char(10) NOT NULL,
  CONSTRAINT [PK_Players] PRIMARY KEY CLUSTERED ([userid]),
  );

 INSERT INTO Players (userid,name) VALUES
 (0,'Tim'),
 (1,'John'),
 (2,'Amy'),
 (3,'Stacy'),
 (4,'Craig'),
 (5,'Adam'),
 (6,'Rachael'),
 (7,'Steve'),
 (8,'Mitch');

 CREATE TABLE Teams (
   teamid int,
   team_name char(10),
   player0 int,
   player1 int,
   player2 int,
   player3 int
   CONSTRAINT [PK_Teams] PRIMARY KEY CLUSTERED ([teamid]),
   -- Says that each of the Player# must correspond to a userid in the Players Table
   CONSTRAINT [p0_2_player_tbl] FOREIGN KEY ([player0]) REFERENCES [dbo].[Players] ([userid]),
   CONSTRAINT [p1_2_player_tbl] FOREIGN KEY ([player1]) REFERENCES [dbo].[Players] ([userid]),
   CONSTRAINT [p2_2_player_tbl] FOREIGN KEY ([player2]) REFERENCES [dbo].[Players] ([userid]),
   CONSTRAINT [p3_2_player_tbl] FOREIGN KEY ([player3]) REFERENCES [dbo].[Players] ([userid]),
   );

INSERT INTO Teams(teamid, team_name, player0,player1,player2,player3) VALUES
(0,'green',0,1,2,3),
(1,'red'  ,4,2,5,0),
(2,'blue' ,6,7,8,2),
-- (3,'black',2,2,2,NULL);

      

        
        
        
      

    

So far, I have managed to find all players on multiple teams

-- this portion in parenthesis Finds all the players on multiple teams
SELECT userid,name,count(*) as 'Num Occurances'
FROM Players
INNER JOIN Teams 
   ON player0=Players.Userid 
   OR player1=Players.Userid 
   OR player2=Players.Userid 
   OR player3=Players.Userid 
GROUP BY userid,name
HAVING COUNT(*) > 1;

      

        
        
        
      

    

I also wrote a sample code to find out which team a player belongs to. As you can see I was clearly lookinguserid = 0

/*I think I want to take the userids returned, and fnd what team 
they are on*/
SELECT teamid,team_name
FROM Teams
WHERE 
     Player0=0 -- Instead of typing the ID directly
  OR Player1=0 -- I'd like to get it from the 
  OR Player2=0 -- previous query
  OR Player3=0
;

      

        
        
        
      

    

Ultimately my goal is to make a nested query where the output of my first query of players across multiple teams will contribute to my teamid search query. I have worked this out so far, but it doesn't work exactly as I would like

/*Issues combining the two statements, I'm not sure how to do this
 I made this attempt, but It not quite right, it list every team
 and the two players, (thing of oring Player0=0 OR Player0=2
 I want a list of each player and the teams they belong to*/
SELECT name,team_name
FROM Players,Teams
WHERE userid IN (
-- this portion in parenthesis Finds all the players on multiple teams
SELECT userid -- ,name,count(*) as 'Num Occurances'
FROM Players
INNER JOIN Teams 
   ON player0=Players.Userid 
   OR player1=Players.Userid 
   OR player2=Players.Userid 
   OR player3=Players.Userid 
GROUP BY userid,name
HAVING COUNT(*) > 1
)  GROUP BY name,team_name
ORDER BY name ASC;

      

        
        
        
      

    

However, this gives me a command with any duplicates (consider making Player0 = 0 OR player0 = 2) as shown below in red

What I want is like a "for everyone" loop. Where we are looking for commands from the EACH of a player returned from a previous request, rather than looking for a command from ANY player returned from a previous request. You can see the result of my goal below (in green)