Feed sql query output to a new sql in a "for-each" style

0

I am trying to do a nested query and I am having a bit of trouble. I have two tables (simplified for this question) with Player information, and another with team information. My ultimate goal is to find players that are on more than one team, and then Print out what team they are on.

I’ve created a sqlfiddle for testing using MS SQL Server 2014. Here is a small pictorial representation of my problem (note that I have omitted the last Team from this test set in my database initialization code):

[EDIT:] sqlfiddle site is having some issues so I’ve attached my DB initialization code

 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);

enter image description here

So far I’ve managed to find all the 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;

find duplicates result

I’ve also wrote sample code to fidn out which team the player belongs to. As you can see I’ve explicitly searched for userid = 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
;

resolve names to teams

Ultimately my goal is to do a nested query where the output from my first query of players on multiple teams will be the input to my teamid lookup query. I’ve developed this so far, but it isn’t working exactly how I’d like it to

/*Issues combining the two statements, I'm not sure how to do this
 I made this attempt, but It's 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 is giving me every team with any duplicates (think of doing Player0=0 OR player0=2) as seen below in red

current incorrect sql output

What I want is something similar to a “for each” style loop. Where we search for the teams of EACH player returned from the previous query rather than a search for the team of ANY player returned form the previous query. You can see my goal output below (in green)

desired SQL ouput

Link: Feed sql query output to a new sql in a "for-each" style
Source: Stack Sql

Share.

About Author

Leave A Reply