quote:

---

I have two tables
1. Player having names and id of players
Data
PLAYERID PLAYERNAME
1 Rondo
2 Allen
3 Pierce
4 Garnett
5 Perkins

2. Plays having id of team with which player is linked
Data
PLAYERID TEAMID
1 1
1 2
1 3
2 1
2 3
3 1
3 3

Now i need query to find the names of players who are linked with exact same number of teams.

Answer is aLLEN,Pierce ( as they are playing for team 1, 3)
AND Garnett,Perkins ( they are not playing for any team)

---

WITH
cte AS (
    SELECT  a.PLAYERID, PLAYERNAME, TEAMID
    FROM    Table1 a
    LEFT    JOIN Table2 b
      ON    a.PLAYERID = b.PLAYERID ),
sub AS (
    SELECT  DISTINCT PLAYERNAME, (
                SELECT  CAST(TEAMID AS varchar(10))+','
                FROM    cte AS b
                WHERE   a.PLAYERID = b.PLAYERID
                FOR  XML PATH('') ) TEAMS  
    FROM    cte a   )
SELECT  (
            SELECT  CAST(PLAYERNAME AS varchar(100))+','
            FROM    sub a
            WHERE   a.TEAMS = b.TEAMS
            FOR  XML PATH('') )
FROM    sub b
GROUP   BY TEAMS
HAVING  COUNT(*) > 1; 

with cte
  as (select a.playerid
            ,a.playername
            ,count(b.teamid) as teamcount
        from table1 as a
             left outer join table2 as b
                          on b.playerid=a.playerid
       group by a.playerid
               ,a.playername
     )
select a.playername
      ,a.teamcount
  from cte as a
       inner join (select teamcount
                     from cte
                    group by teamcount
                    having count(*)>1
                  ) as b
               on b.teamcount=a.teamcount
 order by a.teamcount
         ,a.playername

playername teamcount
Garnett    0
Perkins    0
Allen      2
Peirce     2

with cte
  as (select a.playerid
            ,a.playername
            ,count(b.teamid) as teamcount
        from table1 as a
             left outer join table2 as b
                          on b.playerid=a.playerid
       group by a.playerid
               ,a.playername
     )
select stuff((select ','+b.playername
                from cte as b
               where b.teamcount=a.teamcount
                 for xml path('')
             ),1,1,''
            ) as players
      ,a.teamcount
  from cte as a
 group by a.teamcount
 having count(*)>1
 order by a.teamcount

players         teamcount
Garnett,Perkins 0
Allen,Peirce    2